Re: sql/json miscellaneous issue
Hi,
On Tue, Jun 25, 2024 at 1:53 PM jian he wrote:
> On Tue, Jun 25, 2024 at 11:23 AM Amit Langote 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
Re: sql/json miscellaneous issue
On Tue, Jun 25, 2024 at 11:23 AM Amit Langote wrote:
>
> On Tue, Jun 25, 2024 at 12:18 PM jian he wrote:
> > On Mon, Jun 24, 2024 at 7:46 PM Amit Langote
> > wrote:
> > > On Mon, Jun 24, 2024 at 7:04 PM jian he
> > > 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
From dbc50acce12efcd25e7c55c51609e125e1545439 Mon Sep 17 00:00:00 2001
From: jian he
Date: Tue, 25 Jun 2024 12:35:40 +0800
Subject: [PATCH v1 1/1] document how json_value dealing with jsonb 'null'
---
doc/src/sgml/func.sgml | 13 +
1 file changed, 9 insertions(+), 4 deletions(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 26092696..f6ec96ae 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -18772,7 +18772,7 @@ ERROR: jsonpath array subscript is out of bounds
The ON EMPTY clause specifies the behavior if
evaluating path_expression yields no value
at all. The default when ON EMPTY is not specified
-is to return a null value.
+is to return an SQL NULL value.
The ON ERROR clause specifies the
@@ -18781,7 +18781,7 @@ ERROR: jsonpath array subscript is out of bounds
coerce the result
Re: sql/json miscellaneous issue
On Tue, Jun 25, 2024 at 12:18 PM jian he wrote:
> On Mon, Jun 24, 2024 at 7:46 PM Amit Langote wrote:
> > On Mon, Jun 24, 2024 at 7:04 PM jian he 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
Re: sql/json miscellaneous issue
On Mon, Jun 24, 2024 at 7:46 PM Amit Langote wrote:
>
> Hi,
>
> On Mon, Jun 24, 2024 at 7:04 PM jian he 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)
Re: sql/json miscellaneous issue
Hi,
On Mon, Jun 24, 2024 at 7:04 PM jian he 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
Re: sql/json miscellaneous issue
Hi, On Mon, Jun 24, 2024 at 8:02 PM Stepan Neretin wrote: > Hi! > > I also noticed a very strange difference in behavior in these two queries, it > seems to me that although it returns a string by 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
Re: sql/json miscellaneous issue
On Mon, Jun 24, 2024 at 5:05 PM jian he 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'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! I also noticed a very strange difference in behavior in these two queries, it seems to me that although it returns a string by 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) Best regards, Stepan Neretin.
