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

č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:

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?

Regards

Pavel

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&gt;:

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