I've just realized that. I used it as an equivalent of the standard
`JSON_QUERY` that returns a JSON value. If the expression matches multiple
values, it can wrap them in a JSON array.
Now I'm surprised that a set-returning function is even allowed in SELECT
clause where the values have to be scalar. I tried another query with even
weirder result:
SELECT jsonb_path_query('[1,2,2]', '$[*]?(@ > 1)') expr1,
jsonb_path_query('[1,2,3]', '$[*]?(@ > 0)') expr2
+--------+-------+
| expr1 | expr2 |
+--------+-------+
| 2 | 1 |
| 2 | 2 |
| (null) | 3 |
| | |
+--------+-------+
Is it documented somewhere how is the set-typed result supposed to work?
Also how come a set contains two elements with the same value?
Viliam
On Wed, Mar 23, 2022 at 6:00 PM Thomas Kellerer <sham...@gmx.net> wrote:
> Viliam Ďurina schrieb am 23.03.2022 um 17:56:
> > Hello all,
> >
> > I'm experimenting with JSON-path functions, and stumbled upon this query:
> >
> > SELECT jsonb_path_query('[1,2,3]', '$[*]?(@ == 4)')
> >
> > It returns 0 rows. I expected it to return one row with `null` value.
> > Isn't it the case that `SELECT <some expression>` should always
> > return 1 row?
>
> jsonb_path_query is a set returning function, so it's actually more like
> this:
>
> SELECT *
> FROM jsonb_path_query('[1,2,3]', '$[*]?(@ == 4)')
>
> Then it's obvious why no row is returned.
>
> That's one of the reasons I never use set-returning functions in the
> SELECT list.
>
>
>
>
