Subject: Re: sql/json miscellaneous issue

From: Amit Langote <[email-masked]>
Date: Mon, 24 Jun 2024 20:46:36 +0900

Hi,

On Mon, Jun 24, 2024 at 7:04 PM jian he <[email-masked]> 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

---

From: jian he <[email-masked]>
Date: Tue, 25 Jun 2024 11:18:16 +0800

> SELECT * FROM JSON_query (jsonb 'null', '$' returning jsonb);

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)