Re: sql/json miscellaneous issue

On Mon, Jun 24, 2024 at 7:46 PM Amit Langote <amitlangote09(at)gmail(dot)com> wrote:
>
> Hi,
>
> On Mon, Jun 24, 2024 at 7:04 PM jian he <jian(dot)universality(at)gmail(dot)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)