Content-Type: multipart/signed;
	boundary="Apple-Mail=_28492152-EE83-44A3-B56C-BCA02AE3C45D";
	protocol="application/pgp-signature";
	micalg=pgp-sha256
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3731.700.6\))
Subject: Re: JSON Path and GIN Questions
From: "David E. Wheeler" <david@justatheory.com>
In-Reply-To: <1093370370.83403.1694902436862@office.mailbox.org>
Date: Sat, 16 Sep 2023 19:41:12 -0400
Cc: pgsql-hackers@lists.postgresql.org
Message-Id: <A505EDAB-ED33-4F6E-955F-9FBCFC842415@justatheory.com>
References: <15DD78A5-B5C4-4332-ACFE-55723259C07F@justatheory.com>
 <133696180.303713.1694566807910@office.mailbox.org>
 <7A19C6A6-DEB4-426D-BC6E-9F35A691E567@justatheory.com>
 <1093370370.83403.1694902436862@office.mailbox.org>
To: Erik Wienhold <ewie@ewie.name>
Archived-At: <https://www.postgresql.org/message-id/A505EDAB-ED33-4F6E-955F-9FBCFC842415%40justatheory.com>
Precedence: bulk


--Apple-Mail=_28492152-EE83-44A3-B56C-BCA02AE3C45D
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=utf-8

On Sep 16, 2023, at 18:13, Erik Wienhold <ewie@ewie.name> wrote:

> 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().
>=20
> It kinda works in strict mode:
>=20
> SELECT jsonb_path_query(:'json', 'strict $.track.segments[*].location =
? (@[*] < 14)');
>=20
>    jsonb_path_query
> -----------------------
>  [47.763, 13.4034]
>  [47.706, 13.2635]
> (2 rows)
>=20
> But it does not remove elements from the matching arrays.  Which I =
don't even
> 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.

Yes, this is what I expected. It means =E2=80=9Cselect the location =
array if any of its contents is less that 14.=E2=80=9D

I don=E2=80=99t understand why it=E2=80=99s different in lax mode, =
though, as `@[*]` is not a structural error; it confirms to the schema, =
as the docs say. The flattening in this case seems weird.

Ah, here=E2=80=99s why:, from the docs:

"Besides, comparison operators automatically unwrap their operands in =
the lax mode, so you can compare SQL/JSON arrays out-of-the-box.=E2=80=9D

There follow some discussion of the need to specify `[*]` on segments in =
strict mode, but since that=E2=80=99s exactly what my example does (and =
the same for the locations array inside the filter), it doesn=E2=80=99t =
seem right to me that it would be unwrapped here.

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

Wow fun, and yeah, it makes sense to take things apart in SQL for this =
sort of thing!

Best,

David


--Apple-Mail=_28492152-EE83-44A3-B56C-BCA02AE3C45D
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
	filename=signature.asc
Content-Type: application/pgp-signature;
	name=signature.asc
Content-Description: Message signed with OpenPGP

-----BEGIN PGP SIGNATURE-----

iQIzBAEBCAAdFiEEd+KfmbOQis6ZycGGkt9idPjIgfEFAmUGPRgACgkQkt9idPjI
gfEiTxAApLi8Q+LJMeG6QgPRxZgv6MXFRmeHFUk3VSA6Ks0ubHtWA6RTJHS/4NlQ
mzHQBPGNFIKVmawUuTI3O7W3ZkIq1GDuImcgvKDHGx9KI87yjRZMCKoSXm7gWp7N
B9ezpDNb+r+CLMOe2GcJj9YaJVDDiAUAnbZez9Ev4D3+mxyvxTFDUS8rvm77riBJ
WtwbxDRu0haTK3fBlJia3dgRzeg1dxwEr63LBzEADUFQHQ65XevZRbc23gSUbfva
Bg5WqLks0BC4MbWXKLlRaRay6i9C+7CHRY/Y0CaOLQlglfCVq6TN7I0BxUkdjt8q
AwdMzcgnr/BN0+f4hK0Sc0HDyahHjuaisZ9Ot2VaGDAIJbcio24St9InDGuR4Kbb
Sg09bwi8906bO6ip8YNitNhfCW2CuJaBJs7ZZ0sln+TpHH2+DhYmbj0OLYV5uUke
XuAhko15WKkqjIkRxtqIW6qVgvVdxYu6PwzW/JAF582sE//TXuGP1WF3eEX6RKyx
xXEqITh/EXXt3NmXp2cNOKuNcuLA5+s2YjG1L524tOR6rkhZD4s1+ozHWBmPkRxt
T2m3fBVkapFycUxrqf7Q/E3guPc8zoYLUGFH6MsfI11SgtTskhgsNYWplZSql+2u
cJZOGvv80x7ROh/7QFL9LtgtHw+hsOFAYbkDwAZCMZYkuMnQ3OM=
=UxQ5
-----END PGP SIGNATURE-----

--Apple-Mail=_28492152-EE83-44A3-B56C-BCA02AE3C45D--