Jsonpath ** vs lax mode

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

Jsonpath ** vs lax mode

Alexander Korotkov-4
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

------
Regards,
Alexander Korotkov

jsonpath_double_star_strict_mode.patch (8K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Jsonpath ** vs lax mode

Álvaro Herrera
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


Reply | Threaded
Open this post in threaded view
|

Re: Jsonpath ** vs lax mode

Alexander Korotkov-4
Hi, Alvaro!

Thank you for your feedback.

On Wed, Jan 20, 2021 at 9:16 PM Alvaro Herrera <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: Jsonpath ** vs lax mode

Thomas Kellerer-4
In reply to this post by Alexander Korotkov-4
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


Reply | Threaded
Open this post in threaded view
|

Re: Jsonpath ** vs lax mode

Álvaro Herrera
In reply to this post by Alexander Korotkov-4
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


Reply | Threaded
Open this post in threaded view
|

Re: Jsonpath ** vs lax mode

Alexander Korotkov-4
In reply to this post by Thomas Kellerer-4
On Thu, Jan 21, 2021 at 12:38 PM Thomas Kellerer <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: Jsonpath ** vs lax mode

Alexander Korotkov-4
In reply to this post by Álvaro Herrera
On Thu, Jan 21, 2021 at 4:35 PM Alvaro Herrera <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: Jsonpath ** vs lax mode

Alexander Korotkov-4
On Mon, Jan 25, 2021 at 6:33 PM Alexander Korotkov <[hidden email]> wrote:

> On Thu, Jan 21, 2021 at 4:35 PM Alvaro Herrera <[hidden email]> 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

jsonpath-double-star-lax-docs.patch (1K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Jsonpath ** vs lax mode

Tom Lane-2
Alexander Korotkov <[hidden email]> 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">