Re: sql/json miscellaneous issue

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

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