Re: sql/json miscellaneous issue - Mailing list pgsql-hackers

Hi,

On Tue, Jun 25, 2024 at 1:53 PM jian he <jian.universality@gmail.com> wrote:
> On Tue, Jun 25, 2024 at 11:23 AM Amit Langote <amitlangote09@gmail.com> wrote:
> > > My thoughts  for the above cases are:
> > > * json_value, json_query main description is the same:
> > > {{Returns the result of applying the SQL/JSON path_expression to the
> > > context_item using the PASSING values.}}
> > > same context_item, same path_expression, for the above cases, the
> > > result should be the same?
> > >
> > > * in json_value, description
> > > {{The extracted value must be a single SQL/JSON scalar item; an error
> > > is thrown if that's not the case. If you expect that extracted value
> > > might be an object or an array, use the json_query function instead.}}
> > > query: `SELECT * FROM JSON_value (jsonb 'null', '$' returning jsonb);`
> > > the returned jsonb 'null' (applying the path expression) is a single
> > > SQL/JSON scalar item.
> > > json_value return jsonb null should be fine
> > >
> > >
> > > However, other database implementations return SQL null,
> > > so I guess returning SQL null is fine)
> > > (based on the doc explanation, returning jsonb null more make sense, imho)
> >
> > If the difference in behavior is not clear from the docs, I guess that
> > means that we need to improve the docs.  Would you like to give a shot
> > at writing the patch?
> >
>
> other databases did mention how json_value deals with  json null. eg.
> [0] mysql description:
> When the data at the specified path consists of or resolves to a JSON
> null literal, the function returns SQL NULL.
> [1] oracle description:
> SQL/JSON function json_value applied to JSON value null returns SQL
> NULL, not the SQL string 'null'. This means, in particular, that you
> cannot use json_value to distinguish the JSON value null from the
> absence of a value; SQL NULL indicates both cases.
>
>
> imitate above, i come up with following:
> "The extracted value must be a single SQL/JSON scalar item; an error
> is thrown if that's not the case. ..."
> to
> "The extracted value must be a single SQL/JSON scalar item; an error
> is thrown if that's not the case.
> If the extracted value is a JSON null, an SQL NULL value will return.
> This means that you cannot use json_value to distinguish the JSON
> value null from evaluating path_expression yields no value at all; SQL
> NULL indicates both cases, to distinguish these two cases, use
> json_query instead.
> "
>
> I also changed from
> ON EMPTY is not specified is to return a null value.
> ON ERROR is not specified is to return a null value.
> to
> The default when ON EMPTY is not specified is to return an SQL NULL value.
> The default when ON ERROR is not specified is to return an SQL NULL value.
>
> [0] https://dev.mysql.com/doc/refman/8.4/en/json-search-functions.html#function_json-value
>
[1]https://docs.oracle.com/en/database/oracle/oracle-database/19/adjsn/function-JSON_VALUE.html#GUID-622170D8-7BAD-4F5F-86BF-C328451FC3BE

Thanks, though the patch at [1], which is a much larger attempt to
rewrite SQL/JSON query function docs, takes care of mentioning this.
Could you please give that one a read?

--
Thanks, Amit Langote

[1] https://www.postgresql.org/message-id/CA%2BHiwqH_vwkNqL3Y0tpnugEaR5-7vU43XSxAC06oZJ6U%3D3LVdw%40mail.gmail.com