Jsonpath ** vs lax mode
Hi!
We have a bug report which says that jsonpath ** operator behaves strangely
in the lax mode [1].
Naturally, the result of this query looks counter-intuitive.
# select jsonb_path_query_array('[{"a": 1, "b": [{"a": 2}]}]', 'lax
$.**.a');
jsonb_path_query_array
------------------------
[1, 1, 2, 2]
(1 row)
But actually, everything works as designed. ** operator reports both
objects and wrapping arrays, while object key accessor automatically
unwraps arrays.
# select x, jsonb_path_query_array(x, '$.a') from jsonb_path_query('[{"a":
1, "b": [{"a": 2}]}]', 'lax $.**') x;
x | jsonb_path_query_array
-----------------------------+------------------------
[{"a": 1, "b": [{"a": 2}]}] | [1]
{"a": 1, "b": [{"a": 2}]} | [1]
1 | []
[{"a": 2}] | [2]
{"a": 2} | [2]
2 | []
(6 rows)
At first sight, we may just say that lax mode just sucks and
counter-intuitive results are expected. But at the second sight, the lax
mode is used by default and current behavior may look too surprising.
My proposal is to make everything after the ** operator use strict mode
(patch attached). I think this shouldn't be backpatched, just applied to
the v14. Other suggestions?
Links
1.
/messages/by-id/16828-2b0229babfad2d8c@postgresql.org
------
Regards,
Alexander Korotkov
Attachments:
jsonpath_double_star_strict_mode.patchapplication/octet-stream; name=jsonpath_double_star_strict_mode.patchDownload+37-15
On 2021-Jan-20, Alexander Korotkov wrote:
My proposal is to make everything after the ** operator use strict mode
(patch attached). I think this shouldn't be backpatched, just applied to
the v14. Other suggestions?
I think changing the mode midway through the operation is strange. What
do you think of requiring for ** that mode is strict? That is, if ** is
used and the mode is lax, an error is thrown.
Thanks
--
�lvaro Herrera Valdivia, Chile
Hi, Alvaro!
Thank you for your feedback.
On Wed, Jan 20, 2021 at 9:16 PM Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
On 2021-Jan-20, Alexander Korotkov wrote:
My proposal is to make everything after the ** operator use strict mode
(patch attached). I think this shouldn't be backpatched, just applied to
the v14. Other suggestions?I think changing the mode midway through the operation is strange. What
do you think of requiring for ** that mode is strict? That is, if ** is
used and the mode is lax, an error is thrown.
Yes, changing mode in midway is a bit strange.
Requiring strict mode for ** is a solution, but probably too restrictive...
What do you think about making just subsequent accessor after ** not
to unwrap arrays. That would be a bit tricky to implement, but
probably that would better satisfy the user needs.
------
Regards,
Alexander Korotkov
Alexander Korotkov schrieb am 20.01.2021 um 18:13:
We have a bug report which says that jsonpath ** operator behaves strangely in the lax mode [1].
That report was from me ;)
Thanks for looking into it.
At first sight, we may just say that lax mode just sucks and
counter-intuitive results are expected. But at the second sight, the
lax mode is used by default and current behavior may look too
surprising.
I personally would be fine with the manual stating that the Postgres extension
to the JSONPath processing that allows a recursive lookup using ** requires strict
mode to work properly.
It should probably be documented in chapter 9.16.2 "The SQL/JSON Path Language",
maybe with a little warning in the description of jsonb_path_query** and in
chapter 8.14.16 as well (or at least that's were I would expect such a warning)
Regards
Thomas
On 2021-Jan-21, Alexander Korotkov wrote:
Requiring strict mode for ** is a solution, but probably too restrictive...
What do you think about making just subsequent accessor after ** not
to unwrap arrays. That would be a bit tricky to implement, but
probably that would better satisfy the user needs.
Hmm, why is it too restrictive? If the user needs to further drill into
the JSON, can't they chain json_path_query calls, specifying (or
defaulting to) lax mode for the part doesn't include the ** expression?
--
�lvaro Herrera Valdivia, Chile
On Thu, Jan 21, 2021 at 12:38 PM Thomas Kellerer <shammat@gmx.net> wrote:
Alexander Korotkov schrieb am 20.01.2021 um 18:13:
We have a bug report which says that jsonpath ** operator behaves strangely in the lax mode [1].
That report was from me ;)
Thanks for looking into it.
At first sight, we may just say that lax mode just sucks and
counter-intuitive results are expected. But at the second sight, the
lax mode is used by default and current behavior may look too
surprising.I personally would be fine with the manual stating that the Postgres extension
to the JSONPath processing that allows a recursive lookup using ** requires strict
mode to work properly.It should probably be documented in chapter 9.16.2 "The SQL/JSON Path Language",
maybe with a little warning in the description of jsonb_path_query** and in
chapter 8.14.16 as well (or at least that's were I would expect such a warning)
Thank you for reporting :)
Yeah, documenting the current behavior is something "must have". If
even we find the appropriate behavior change, I don't think it would
be backpatchable. But we need to backpatch the documentation for
sure. So, let's start by fixing the docs.
------
Regards,
Alexander Korotkov
On Thu, Jan 21, 2021 at 4:35 PM Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
On 2021-Jan-21, Alexander Korotkov wrote:
Requiring strict mode for ** is a solution, but probably too restrictive...
What do you think about making just subsequent accessor after ** not
to unwrap arrays. That would be a bit tricky to implement, but
probably that would better satisfy the user needs.Hmm, why is it too restrictive? If the user needs to further drill into
the JSON, can't they chain json_path_query calls, specifying (or
defaulting to) lax mode for the part doesn't include the ** expression?
For sure, there are some walkarounds. But I don't think all the
lax-mode queries involving ** are affected. So, it might happen that
we force users to use strict-mode or chain call even if it's not
necessary. I'm tending to just fix the doc and wait if there are mode
complaints :)
------
Regards,
Alexander Korotkov
On Mon, Jan 25, 2021 at 6:33 PM Alexander Korotkov <aekorotkov@gmail.com> wrote:
On Thu, Jan 21, 2021 at 4:35 PM Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
On 2021-Jan-21, Alexander Korotkov wrote:
Requiring strict mode for ** is a solution, but probably too restrictive...
What do you think about making just subsequent accessor after ** not
to unwrap arrays. That would be a bit tricky to implement, but
probably that would better satisfy the user needs.Hmm, why is it too restrictive? If the user needs to further drill into
the JSON, can't they chain json_path_query calls, specifying (or
defaulting to) lax mode for the part doesn't include the ** expression?For sure, there are some walkarounds. But I don't think all the
lax-mode queries involving ** are affected. So, it might happen that
we force users to use strict-mode or chain call even if it's not
necessary. I'm tending to just fix the doc and wait if there are mode
complaints :)
The patch, which clarifies this situation in the docs is attached.
I'm going to push it if no objections.
------
Regards,
Alexander Korotkov
Attachments:
jsonpath-double-star-lax-docs.patchapplication/octet-stream; name=jsonpath-double-star-lax-docs.patchDownload+18-0
Alexander Korotkov <aekorotkov@gmail.com> writes:
The patch, which clarifies this situation in the docs is attached.
I'm going to push it if no objections.
+1, but the English in this seems a bit shaky. Perhaps more
like the attached?
regards, tom lane