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

From Amit Langote
Subject Re: sql/json miscellaneous issue
Date
Msg-id CA+HiwqEYcPNmvw2sPjVxtkeXEocA=A20zdQ=HXbd8m3LTLWcCA@mail.gmail.com
Whole thread Raw
In response to Re: sql/json miscellaneous issue  (jian he <jian.universality@gmail.com>)
Responses Re: sql/json miscellaneous issue
List pgsql-hackers
On Tue, Jun 25, 2024 at 12:18 PM jian he <jian.universality@gmail.com> wrote:
> On Mon, Jun 24, 2024 at 7:46 PM Amit Langote <amitlangote09@gmail.com> wrote:
> > 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.

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



pgsql-hackers by date:

Previous
From: jian he
Date:
Subject: Re: sql/json miscellaneous issue
Next
From: Степан Неретин
Date:
Subject: Re: Patch bug: Fix jsonpath .* on Arrays