Thread: Unexpected extra row from jsonb_path_query() with a recursive path

(Adding Alexander and Nikita in CC.)

On Sun, Dec 08, 2019 at 12:43:18PM +0100, Jan Przemysław Wójcik wrote:
> select jsonb_path_query('{"data": [{"key": "value"}]}', '$.**.key')
>
> Actual output:
>
>  jsonb_path_query
> ------------------
>  "value"
>  "value"
> (2 rows)
>
> Expected output:
>
>  jsonb_path_query
> ------------------
>  "value"
> (1 row)

So...  .** means that it processes all the levels of the JSON
hierarchy and that it returns all the member values, including all the
members of an array as well as the array itself.  Hence, based on your
previous example we have that:
=# select jsonb_path_query('{"data": [{"key": "value"}]}', '$.**');
       jsonb_path_query
------------------------------
 {"data": [{"key": "value"}]}
 [{"key": "value"}]
 {"key": "value"}
 "value"
(4 rows)

that's what I would expect here with both the array and each one of
its elements to be selected.

Hence the question of Jan would be I guess the following: should the
value of first element of the array be selected or not in this case?
I am not completely sure if that's the wanted behavior or not, but I
would expect {"key": "value"} to be the only part selected, and only
one value to be returned, so this smells like a bug.  Jan, is that
what you meant?

Alexander, Nikita, any opinions to offer?
--
Michael

On Mon, 9 Dec 2019 at 08:29 Michael Paquier <michael@paquier.xyz> wrote:
>
> (Adding Alexander and Nikita in CC.)
>
> On Sun, Dec 08, 2019 at 12:43:18PM +0100, Jan Przemysław Wójcik wrote:
> > select jsonb_path_query('{"data": [{"key": "value"}]}', '$.**.key')
> >
> > Actual output:
> >
> >  jsonb_path_query
> > ------------------
> >  "value"
> >  "value"
> > (2 rows)
> >
> > Expected output:
> >
> >  jsonb_path_query
> > ------------------
> >  "value"
> > (1 row)
>
> So...  .** means that it processes all the levels of the JSON
> hierarchy and that it returns all the member values, including all the
> members of an array as well as the array itself.  Hence, based on your
> previous example we have that:
> =# select jsonb_path_query('{"data": [{"key": "value"}]}', '$.**');
>        jsonb_path_query
> ------------------------------
>  {"data": [{"key": "value"}]}
>  [{"key": "value"}]
>  {"key": "value"}
>  "value"
> (4 rows)
>
> that's what I would expect here with both the array and each one of
> its elements to be selected.
>
> Hence the question of Jan would be I guess the following: should the
> value of first element of the array be selected or not in this case?
> I am not completely sure if that's the wanted behavior or not, but I
> would expect {"key": "value"} to be the only part selected, and only
> one value to be returned, so this smells like a bug.  Jan, is that
> what you meant?

Yes, there is the only object with the attribute 'key' and its value
should be returned. Note, that a non-recursive path on the same JSON
correctly returns a single row:

select jsonb_path_query('{"data": [{"key": "value"}]}', '$.*.key')

 jsonb_path_query
------------------
 "value"
(1 row)

>
> Alexander, Nikita, any opinions to offer?
> --
> Michael

--
Jan

On Mon, Dec 09, 2019 at 09:37:23AM +0100, Jan Przemysław Wójcik wrote:
> Yes, there is the only object with the attribute 'key' and its value
> should be returned. Note, that a non-recursive path on the same JSON
> correctly returns a single row:
>
> select jsonb_path_query('{"data": [{"key": "value"}]}', '$.*.key')
>
>  jsonb_path_query
> ------------------
>  "value"
> (1 row)

Yeah, that result is right.  Something looks wrong regarding the
lookup of an array's elements when doing a recursion.  Let's wait a
bit to see if Nikita or Alexander have anything to share as they
implemented the feature.  If not, I'll try to take a closer look at
this issue.
--
Michael