Thread: json_query - redundant result

On 2022-04-28 Th 04:16, Pavel Stehule wrote:
> Hi
>
> I am learning new JSON API, and I am not sure, how the result of
> JSON_QUERY in one case is correct. So I am asking here
>
> (2022-04-28 10:13:26) postgres=# SELECT JSON_QUERY(jsonb '[{"a":10,
> "b": 20}, {"a": 30, "b":100}]', '$.**.a' with wrapper);
> ┌──────────────────┐
> │    json_query    │
> ╞══════════════════╡
> │ [10, 30, 10, 30] │
> └──────────────────┘
> (1 row)
>
> Is this result correct? I am expecting just [10, 30]


It's just a wrapper around jsonb_path_query, which hasn't changed.


# SELECT jsonb_path_query(jsonb '[{"a":10, "b": 20}, {"a": 30,
"b":100}]', '$.**.a');
 jsonb_path_query
------------------
 10
 30
 10
 30
(4 rows)


If that's a bug it's not a new one - release 14 gives the same result.


cheers


andrew


--
Andrew Dunstan
EDB: https://www.enterprisedb.com

Andrew Dunstan <andrew@dunslane.net> writes:
> On 2022-04-28 Th 04:16, Pavel Stehule wrote:
>> Is this result correct? I am expecting just [10, 30]

> It's just a wrapper around jsonb_path_query, which hasn't changed.

> # SELECT jsonb_path_query(jsonb '[{"a":10, "b": 20}, {"a": 30,
> "b":100}]', '$.**.a');
>  jsonb_path_query
> ------------------
>  10
>  30
>  10
>  30
> (4 rows)

> If that's a bug it's not a new one - release 14 gives the same result.

I'm pretty clueless in this area, but I think this might have to do with
the "lax mode" described in 9.16.2.1:

https://www.postgresql.org/docs/devel/functions-json.html#FUNCTIONS-SQLJSON-PATH

regression=# SELECT jsonb_path_query(jsonb '[{"a":10, "b": 20}, {"a": 30,
regression'# "b":100}]', '$.**.a');
 jsonb_path_query
------------------
 10
 30
 10
 30
(4 rows)

regression=# SELECT jsonb_path_query(jsonb '[{"a":10, "b": 20}, {"a": 30,
"b":100}]', 'strict $.**.a');
 jsonb_path_query
------------------
 10
 30
(2 rows)

Maybe these SQL-standard syntaxes ought to default to strict mode?

            regards, tom lane

On 2022-04-28 Th 10:06, Pavel Stehule wrote:
>
>
> čt 28. 4. 2022 v 16:00 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:
>
>     Andrew Dunstan <andrew@dunslane.net> writes:
>     > On 2022-04-28 Th 04:16, Pavel Stehule wrote:
>     >> Is this result correct? I am expecting just [10, 30]
>
>     > It's just a wrapper around jsonb_path_query, which hasn't changed.
>
>     > # SELECT jsonb_path_query(jsonb '[{"a":10, "b": 20}, {"a": 30,
>     > "b":100}]', '$.**.a');
>     >  jsonb_path_query
>     > ------------------
>     >  10
>     >  30
>     >  10
>     >  30
>     > (4 rows)
>
>     > If that's a bug it's not a new one - release 14 gives the same
>     result.
>
>     I'm pretty clueless in this area, but I think this might have to
>     do with
>     the "lax mode" described in 9.16.2.1 <http://9.16.2.1>:
>
>     https://www.postgresql.org/docs/devel/functions-json.html#FUNCTIONS-SQLJSON-PATH
>
>     regression=# SELECT jsonb_path_query(jsonb '[{"a":10, "b": 20},
>     {"a": 30,
>     regression'# "b":100}]', '$.**.a');
>      jsonb_path_query
>     ------------------
>      10
>      30
>      10
>      30
>     (4 rows)
>
>     regression=# SELECT jsonb_path_query(jsonb '[{"a":10, "b": 20},
>     {"a": 30,
>     "b":100}]', 'strict $.**.a');
>      jsonb_path_query
>     ------------------
>      10
>      30
>     (2 rows)
>
>     Maybe these SQL-standard syntaxes ought to default to strict mode?
>
>
> It looks like a perfect trap, although it is documented.
>
> I don't think the default strict mode is better. Maybe disallow .** in
> lax mode?
>
>


Yeah, having strict the default for json_query and lax the default for
jsonb_path_query seems like a recipe for serious confusion.


I have no opinion about .** in lax mode.


cheers


andrew



--
Andrew Dunstan
EDB: https://www.enterprisedb.com