pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #16828: duplicate results when using ** recursive expression in JSON path
3+ messages / 3 participants
[nested] [flat]
Commits from this thread:
2021-01-29 b41645460a 9915fe2296 bd6366129b Document behavior of the .** jsonpath accessor in the lax mode
* BUG #16828: duplicate results when using ** recursive expression in JSON path
@ 2021-01-17 10:34 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 3+ messages in thread
From: PG Bug reporting form @ 2021-01-17 10:34 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: shammat@gmx.net
The following bug has been logged on the website:
Bug reference: 16828
Logged by: Thomas Kellerer
Email address: shammat@gmx.net
PostgreSQL version: 13.1
Operating system: Windows 10, CentOS 8
Description:
The following query
select jsonb_path_query_array(col, '$.**.itemName')
from (
values ('{"items": [{"itemName": "a", "items": [{"itemName":
"b"}]}]}'::jsonb)
) as t(col)
returns ["a", "a", "b", "b"] but should return only ["a", "b"] as each value
only appears once in the JSON value.
This also happens on Postgres 12.x
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #16828: duplicate results when using ** recursive expression in JSON path
@ 2021-01-17 19:42 Tom Lane <tgl@sss.pgh.pa.us>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2021-01-17 19:42 UTC (permalink / raw)
To: shammat@gmx.net; +Cc: Alexander Korotkov <aekorotkov@gmail.com>; pgsql-bugs@lists.postgresql.org
PG Bug reporting form <noreply@postgresql.org> writes:
> The following query
> select jsonb_path_query_array(col, '$.**.itemName')
> from (
> values ('{"items": [{"itemName": "a", "items": [{"itemName":
> "b"}]}]}'::jsonb)
> ) as t(col)
> returns ["a", "a", "b", "b"] but should return only ["a", "b"] as each value
> only appears once in the JSON value.
jsonpath_exec.c is a muddle of undocumented code, but I think
I found where the duplication is occurring: in executeAnyItem(),
the first occurrence comes out while recursing down from the
executeItemOptUnwrapTarget call at line 1419 (as of HEAD), and then
the duplicate comes out while recursing down from the executeAnyItem
call at line 1439. So I'd say that that logic for
"ignoreStructuralErrors" needs a rethink. (BTW, why is half of this
code relying on cxt->ignoreStructuralErrors while the other half
receives a passed-down flag? That seems like a recipe for bugs,
especially with the lack of commentary about the reason for it.)
Alexander, git blame says all this code is your fault ...
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #16828: duplicate results when using ** recursive expression in JSON path
@ 2021-01-17 21:24 Alexander Korotkov <aekorotkov@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Alexander Korotkov @ 2021-01-17 21:24 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: shammat@gmx.net, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Sun, Jan 17, 2021 at 10:42 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> PG Bug reporting form <noreply@postgresql.org> writes:
> > The following query
>
> > select jsonb_path_query_array(col, '$.**.itemName')
> > from (
> > values ('{"items": [{"itemName": "a", "items": [{"itemName":
> > "b"}]}]}'::jsonb)
> > ) as t(col)
>
> > returns ["a", "a", "b", "b"] but should return only ["a", "b"] as each
value
> > only appears once in the JSON value.
>
> jsonpath_exec.c is a muddle of undocumented code, but I think
> I found where the duplication is occurring: in executeAnyItem(),
> the first occurrence comes out while recursing down from the
> executeItemOptUnwrapTarget call at line 1419 (as of HEAD), and then
> the duplicate comes out while recursing down from the executeAnyItem
> call at line 1439. So I'd say that that logic for
> "ignoreStructuralErrors" needs a rethink. (BTW, why is half of this
> code relying on cxt->ignoreStructuralErrors while the other half
> receives a passed-down flag? That seems like a recipe for bugs,
> especially with the lack of commentary about the reason for it.)
I think that's not just a bug in the code, but a high-level problem.
** operator enumerates all the subdocuments.
# select * from jsonb_path_query('{"items": [{"itemName": "a", "items":
[{"itemName": "b"}]}]}'::jsonb, '$.**');
jsonb_path_query
--------------------------------------------------------------
{"items": [{"items": [{"itemName": "b"}], "itemName": "a"}]}
[{"items": [{"itemName": "b"}], "itemName": "a"}]
{"items": [{"itemName": "b"}], "itemName": "a"}
[{"itemName": "b"}]
{"itemName": "b"}
"b"
"a"
(7 rows)
After that, .itemName accessor is applied. But in the lax mode this
accessor automatically unwraps the arrays.
# select subres, jsonb_path_query_array(subres, '$.itemName') from
jsonb_path_query('{"items": [{"itemName": "a", "items": [{"itemName":
"b"}]}]}'::jsonb, '$.**') subres;
subres |
jsonb_path_query_array
--------------------------------------------------------------+------------------------
{"items": [{"items": [{"itemName": "b"}], "itemName": "a"}]} | []
[{"items": [{"itemName": "b"}], "itemName": "a"}] | ["a"]
{"items": [{"itemName": "b"}], "itemName": "a"} | ["a"]
[{"itemName": "b"}] | ["b"]
{"itemName": "b"} | ["b"]
"b" | []
"a" | []
(7 rows)
So, everything works as designed, but the design is probably wrong. The
issue here is that ** is our extension of the standard, and it works
strangely in lax mode. But in strict mode everything looks OK.
# select jsonb_path_query_array('{"items": [{"itemName": "a", "items":
[{"itemName": "b"}]}]}'::jsonb, 'strict $.**.itemName');
jsonb_path_query_array
------------------------
["a", "b"]
(1 row)
Should we consider everything that comes after ** always in strict mode?
> Alexander, git blame says all this code is your fault ...
I do agree all faults in this code are mine. I do agree there are faults
in this code. But I don't think *all* this code is fault :)
I'll plan my time to improve the clarity of this code.
------
Regards,
Alexander Korotkov
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2021-01-17 21:24 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-01-17 10:34 BUG #16828: duplicate results when using ** recursive expression in JSON path PG Bug reporting form <noreply@postgresql.org>
2021-01-17 19:42 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-01-17 21:24 ` Alexander Korotkov <aekorotkov@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox