Re: sql/json miscellaneous issue

On Mon, Jun 24, 2024 at 5:05 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've tried a patch to implement it.
> (i raised the issue at
>
> https://www.postgresql.org/message-id/CACJufxFWiCnG3Q7f0m_GdrytPbv29A5OWngCDwKVjcftwzHbTA%40mail.gmail.com
> i think a new thread would be more appropriate).
>
>
>
> current json_value doc:
> "Note that scalar strings returned by json_value always have their
> quotes removed, equivalent to specifying OMIT QUOTES in json_query."
>
> i think there are two exceptions: when the returning data types are
> jsonb or json.
>
>
>
Hi!

I also noticed a very strange difference in behavior in these two queries,
it seems to me that although it returns a string by default, for the boolean
operator it is necessary to return true or false
SELECT * FROM JSON_value (jsonb '1', '$ == "1"' returning jsonb);
json_value
------------

(1 row)

SELECT * FROM JSON_value (jsonb 'null', '$ == "1"' returning jsonb);
json_value
------------
false
(1 row)

Best regards, Stepan Neretin.