Horton

Thread

Commits

  1. Document behavior of the .** jsonpath accessor in the lax mode

  1. Jsonpath ** vs lax mode

    Alexander Korotkov <aekorotkov@gmail.com> — 2021-01-20T17:13:05Z

    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.
https://www.postgresql.org/message-id/16828-2b0229babfad2d8c%40postgresql.org

------
Regards,
Alexander Korotkov

  2. Re: Jsonpath ** vs lax mode

    Alvaro Herrera <alvherre@alvh.no-ip.org> — 2021-01-20T18:16:34Z

    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

  3. Re: Jsonpath ** vs lax mode

    Alexander Korotkov <aekorotkov@gmail.com> — 2021-01-21T09:27:45Z

    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

  4. Re: Jsonpath ** vs lax mode

    shammat@gmx.net — 2021-01-21T09:37:58Z

    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

  5. Re: Jsonpath ** vs lax mode

    Alvaro Herrera <alvherre@alvh.no-ip.org> — 2021-01-21T13:35:10Z

    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

  6. Re: Jsonpath ** vs lax mode

    Alexander Korotkov <aekorotkov@gmail.com> — 2021-01-25T15:31:01Z

    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

  7. Re: Jsonpath ** vs lax mode

    Alexander Korotkov <aekorotkov@gmail.com> — 2021-01-25T15:33:50Z

    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

  8. Re: Jsonpath ** vs lax mode

    Alexander Korotkov <aekorotkov@gmail.com> — 2021-01-28T21:44:17Z

    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

  9. Re: Jsonpath ** vs lax mode

    Tom Lane <tgl@sss.pgh.pa.us> — 2021-01-29T00:02:19Z

    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