Thread: sql/json miscellaneous issue

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've tried a patch to implement it.
(i raised the issue at
https://www.postgresql.org/message-id/CACJufxFWiCnG3Q7f0m_GdrytPbv29A5OWngCDwKVjcftwzHbTA%40mail.gmail.com
i think a new thread would be more appropriate).



current json_value  doc:
"Note that scalar strings returned by json_value always have their
quotes removed, equivalent to specifying OMIT QUOTES in json_query."

i think there are two exceptions: when the returning data types are
jsonb or json.

Hi,

On Mon, Jun 24, 2024 at 8:02 PM Stepan Neretin <sncfmgg@gmail.com> wrote:
> Hi!
>
> I also noticed a very strange difference in behavior in these two queries, it seems to me that although it returns a
stringby default, for the boolean operator it is necessary to return true or false 
> SELECT * FROM JSON_value (jsonb '1', '$ == "1"' returning jsonb);
>  json_value
> ------------
>
> (1 row)
>
>  SELECT * FROM JSON_value (jsonb 'null', '$ == "1"' returning jsonb);
>  json_value
> ------------
>  false
> (1 row)

Hmm, that looks sane to me when comparing the above two queries with
their jsonb_path_query() equivalents:

select jsonb_path_query(jsonb '1', '$ == "1"');
 jsonb_path_query
------------------
 null
(1 row)

select jsonb_path_query(jsonb 'null', '$ == "1"');
 jsonb_path_query
------------------
 false
(1 row)

--
Thanks, Amit Langote

Hi,

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

On Mon, Jun 24, 2024 at 7:46 PM Amit Langote <amitlangote09@gmail.com> wrote:
>
> Hi,
>
> 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.

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)

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?

--
Thanks, Amit Langote

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

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