Content-Type: multipart/signed;
	boundary="Apple-Mail=_16510513-1918-4D74-AB67-23B8FDE389AE";
	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: <133696180.303713.1694566807910@office.mailbox.org>
Date: Sat, 16 Sep 2023 16:26:07 -0400
Cc: pgsql-hackers@lists.postgresql.org
Message-Id: <7A19C6A6-DEB4-426D-BC6E-9F35A691E567@justatheory.com>
References: <15DD78A5-B5C4-4332-ACFE-55723259C07F@justatheory.com>
 <133696180.303713.1694566807910@office.mailbox.org>
To: Erik Wienhold <ewie@ewie.name>
Archived-At: <https://www.postgresql.org/message-id/7A19C6A6-DEB4-426D-BC6E-9F35A691E567%40justatheory.com>
Precedence: bulk


--Apple-Mail=_16510513-1918-4D74-AB67-23B8FDE389AE
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=utf-8

On Sep 12, 2023, at 21:00, Erik Wienhold <ewie@ewie.name> wrote:

>> If so, I=E2=80=99d like to submit a patch to the docs talking about =
this, and
>> suggesting the use of jsonb_path_query() to test paths to see if they =
return
>> a boolean or not.
>=20
> +1

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 perfect 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?

Best,

David

  [1]: =
https://www.postgresql.org/docs/current/functions-json.html#FUNCTIONS-SQLJ=
SON-PATH

--Apple-Mail=_16510513-1918-4D74-AB67-23B8FDE389AE
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+KfmbOQis6ZycGGkt9idPjIgfEFAmUGD18ACgkQkt9idPjI
gfH8fw/5AYmbLATrI4FzmUrwc6kt/SceyuArvDMLaiOKmJr/N/k/lmx9LF2Xo8Xj
Hu0CI3vzB7QMwh7ZyN5OeNcYu8T9yM+UFlTeUOwDQGgu4Sw41CMKKxnnJrvgPYeZ
MKy6yY6TnovK6ZzqgNJ33QhXbT465V2fW34V/+7xR2p9TsSVEwqXsQRcw+zQ3pv6
1kfypGu+VLcU5IBFrdgEyZ7jW9k+mclRu9eCJgYL0ePR9hDA1bzJNtW+5MDZJ44v
9P5tD5Sk513nXqAd1pqqTPTq+dTWIAdqQ7dCVb9JI0eMPtZbsToep0ijoVkQ0REW
taML0E6xFrlyoIxEKBdIUirwMPlzBLBiyRMtV5ZbfqclnFyKJU6nJkivSabNUDTG
/yDK+89jTMSsp8MUlPRahCm3PpqjKpwPJA4bKPe/FKLDLWKTyuPc+epHWYkBfmtB
DZTquWO/czkSip6ibIQFNNFfQaUEkOliN6KDj2GUy+jvrDc7M0rITg7VdbzIV8OE
1wGlETlCOAVk0cAzaAo8FADkjs+F6VHeYfKpKaV0Ovs1tBerohqa6Gq9QlSiZ+FK
Zd5B9jdL/eR6Dd/6TI31k+OrY5EHqKRoHQgLMWChqEElEy82BEw3R4XSzN9izbs9
FOsEJz7nuezhqlBKvcwqcHo/QbGI4tTO+kD1m7ufVF+gbpCh028=
=J54C
-----END PGP SIGNATURE-----

--Apple-Mail=_16510513-1918-4D74-AB67-23B8FDE389AE--