Thread: 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)
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)
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
------
Regards,
Alexander Korotkov
Alexander Korotkov
Attachment
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
Attachment
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
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 4342c8e74f..de1b1b6deb 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -16277,6 +16277,24 @@ strict $.track.segments[*].location
</programlisting>
</para>
+ <para>
+ The <literal>.**</literal> accessor can lead to surprising results
+ when using the lax mode. For instance, the following query selects every
+ <literal>HR</literal> value twice:
+<programlisting>
+lax $.**.HR
+</programlisting>
+ This happens because the <literal>.**</literal> accessor selects both
+ the <literal>segments</literal> array and each of its elements, while
+ the <literal>.HR</literal> accessor automatically unwraps arrays when
+ using the lax mode. To avoid surprising results, we recommend using
+ the <literal>.**</literal> accessor only in the strict mode. The
+ following query selects each <literal>HR</literal> value just once:
+<programlisting>
+strict $.**.HR
+</programlisting>
+ </para>
+
</sect3>
<sect3 id="functions-sqljson-path-operators">