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

On Mon, 9 Dec 2019 at 08:29 Michael Paquier <michael(at)paquier(dot)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