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

On Mon, 9 Dec 2019 at 08:29 Michael Paquier <[email protected]> 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