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

On Tue, Jun 25, 2024 at 11:23 AM Amit Langote <amitlangote09@gmail.com> wrote:
>
> On Tue, Jun 25, 2024 at 12:18 PM jian he <jian.universality@gmail.com> wrote:
> > On Mon, Jun 24, 2024 at 7:46 PM Amit Langote <amitlangote09@gmail.com> wrote:
> > > On Mon, Jun 24, 2024 at 7:04 PM jian he <jian.universality@gmail.com> wrote:
> > > >
> > > > hi.
> > > > the following two queries should return the same result?
> > > >
> > > > SELECT * FROM JSON_query (jsonb 'null', '$' returning jsonb);
> > > > SELECT * FROM JSON_value (jsonb 'null', '$' returning jsonb);
> > >
> > > I get this with HEAD:
> > >
> > > SELECT * FROM JSON_query (jsonb 'null', '$' returning jsonb);
> > >  json_query
> > > ------------
> > >  null
> > > (1 row)
> > >
> > > Time: 734.587 ms
> > > SELECT * FROM JSON_value (jsonb 'null', '$' returning jsonb);
> > >  json_value
> > > ------------
> > >
> > > (1 row)
> > >
> > > Much like:
> > >
> > > SELECT JSON_QUERY('{"key": null}', '$.key');
> > >  json_query
> > > ------------
> > >  null
> > > (1 row)
> > >
> > > Time: 2.975 ms
> > > SELECT JSON_VALUE('{"key": null}', '$.key');
> > >  json_value
> > > ------------
> > >
> > > (1 row)
> > >
> > > Which makes sense to me, because JSON_QUERY() is supposed to return a
> > > JSON null in both cases and JSON_VALUE() is supposed to return a SQL
> > > NULL for a JSON null.
> > >
> > > --
> > > Thanks, Amit Langote
> >
> > hi amit, sorry to bother you again.
>
> No worries.
>
> > 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