Date: Sun, 17 Sep 2023 00:13:56 +0200 (CEST)
From: Erik Wienhold <ewie@ewie.name>
To: "David E. Wheeler" <david@justatheory.com>
Cc: pgsql-hackers@lists.postgresql.org
Message-ID: <1093370370.83403.1694902436862@office.mailbox.org>
In-Reply-To: <7A19C6A6-DEB4-426D-BC6E-9F35A691E567@justatheory.com>
References: <15DD78A5-B5C4-4332-ACFE-55723259C07F@justatheory.com>
 <133696180.303713.1694566807910@office.mailbox.org>
 <7A19C6A6-DEB4-426D-BC6E-9F35A691E567@justatheory.com>
Subject: Re: JSON Path and GIN Questions
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
Importance: Normal
Archived-At: <https://www.postgresql.org/message-id/1093370370.83403.1694902436862%40office.mailbox.org>
Precedence: bulk

On 16/09/2023 22:26 CEST David E. Wheeler <david@justatheory.com> wrote:

> I=E2=80=99ve started work on this; there=E2=80=99s so much to learn! Here=
=E2=80=99s a new example
> that surprised me a bit. Using the GPS tracker example from the docs [1]
> loaded into a `:json` psql variable, this output of this query makes perf=
ect
> sense to me:
>
> david=3D# select jsonb_path_query(:'json', '$.track.segments.location[*] =
? (@ < 14)');
>  jsonb_path_query
> ------------------
>  13.4034
>  13.2635
>
> Because `[*]` selects all the values. This, however, I did not expect:
>
> david=3D# select jsonb_path_query(:'json', '$.track.segments.location ? (=
@[*] < 14)');
>  jsonb_path_query
> ------------------
>  13.4034
>  13.2635
> (2 rows)
>
> I had expected it to return two single-value arrays, instead:
>
>  [13.4034]
>  [13.2635]
>
> It appears that the filter expression is doing some sub-selection, too.
> Is that expected?

Looks like the effect of lax mode which may unwrap arrays when necessary [1=
].
The array unwrapping looks like the result of jsonb_array_elements().

It kinda works in strict mode:

=09SELECT jsonb_path_query(:'json', 'strict $.track.segments[*].location ? =
(@[*] < 14)');
=09
=09   jsonb_path_query
=09-----------------------
=09 [47.763, 13.4034]
=09 [47.706, 13.2635]
=09(2 rows)

But it does not remove elements from the matching arrays.  Which I don't ev=
en
expect here because the path specifies the location array as the object to =
be
returned.  The filter expression then only decides whether to return the
location array or not.  Nowhere in the docs does it say that the filter
expression itself removes any elements from a matched array.

Here's a query that filter's out individual array elements.  It's quite a
mouthful (especially to preserve the order of array elements):

=09WITH location AS (
=09  SELECT loc, row_number() OVER () AS array_num
=09  FROM jsonb_path_query(:'json', 'strict $.track.segments[*].location') =
loc
=09),
=09element AS (
=09  SELECT array_num, e.num AS elem_num, e.elem
=09  FROM location
=09    CROSS JOIN jsonb_array_elements(loc) WITH ORDINALITY AS e (elem, num=
)
=09)
=09SELECT jsonb_agg(elem ORDER BY elem_num)
=09FROM element
=09WHERE jsonb_path_exists(elem, '$ ? (@ < 14)')
=09GROUP BY array_num;
=09
=09   jsonb_agg
=09---------------
=09 [13.2635]
=09 [13.4034]
=09(2 rows)

[1] https://www.postgresql.org/docs/current/functions-json.html#STRICT-AND-=
LAX-MODES

--
Erik