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

From jian he
Subject Re: sql/json miscellaneous issue
Date
Msg-id CACJufxFexRhWUQcdnqE0ZgnwHZMHGsMUfu7sx9MHx-SOo7pe0w@mail.gmail.com
Whole thread Raw
In response to Re: sql/json miscellaneous issue  (Amit Langote <amitlangote09@gmail.com>)
Responses Re: sql/json miscellaneous issue
List pgsql-hackers
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)



pgsql-hackers by date:

Previous
From: wenhui qiu
Date:
Subject: Re: Support "Right Semi Join" plan shapes
Next
From: Amit Langote
Date:
Subject: Re: sql/json miscellaneous issue