From: "David E. Wheeler" <david@justatheory.com>
Content-Type: multipart/signed;
	boundary="Apple-Mail=_9A554064-C8C0-446D-B373-CF39ABB9123A";
	protocol="application/pgp-signature";
	micalg=pgp-sha256
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3731.700.6\))
Subject: JSON Path and GIN Questions
Message-Id: <15DD78A5-B5C4-4332-ACFE-55723259C07F@justatheory.com>
Date: Tue, 12 Sep 2023 20:16:53 -0400
To: pgsql-hackers@lists.postgresql.org
Archived-At: <https://www.postgresql.org/message-id/15DD78A5-B5C4-4332-ACFE-55723259C07F%40justatheory.com>
Precedence: bulk


--Apple-Mail=_9A554064-C8C0-446D-B373-CF39ABB9123A
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=utf-8

Greetings Hackers,

Been a while! I=E2=80=99m working on some experiments with JSONB columns =
and GIN indexes, and have operated on the assumption that JSON Path =
operations would take advantage of GIN indexes, with json_path_ops as a =
nice optimization. But I=E2=80=99ve run into what appear to be some =
inconsistencies and oddities I=E2=80=99m hoping to figure out with your =
help.

For the examples in this email, I=E2=80=99m using this simple table:

CREATE TABLE MOVIES (id SERIAL PRIMARY KEY, movie JSONB NOT NULL);
\copy movies(movie) from PROGRAM 'curl -s =
https://raw.githubusercontent.com/prust/wikipedia-movie-data/master/movies=
.json | jq -c ".[]" | sed "s|\\\\|\\\\\\\\|g"';
create index on movies using gin (movie);
analyze movies;

That gives me a simple table with around 3600 rows. Not a lot of data, =
but hopefully enough to demonstrate the issues.

Issue 1: @@ vs @?
-----------------

I have been confused as to the difference between @@ vs @?: Why do these =
return different results?

david=3D# select id from movies where movie @@ '$ ?(@.title =3D=3D "New =
Life Rescue")';
 id
----
(0 rows)

david=3D# select id from movies where movie @? '$ ?(@.title =3D=3D "New =
Life Rescue")';
 id
----
 10
(1 row)

I posted this question on Stack Overflow =
(https://stackoverflow.com/q/77046554/79202), and from the suggestion I =
got there, it seems that @@ expects a boolean to be returned by the path =
query, while @? wraps it in an implicit exists(). Is that right?

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.


Issue 2: @? Index Use
---------------------

=46rom Oleg=E2=80=99s (happy belated birthday!) notes =
(https://github.com/obartunov/sqljsondoc/blob/master/jsonpath.md#jsonpath-=
operators):


> Operators @? and @@ are interchangeable:
>=20
>     js @? '$.a' <=3D> js @@ 'exists($.a)=E2=80=99
>     js @@ '$.a =3D=3D 1' <=3D> js @? '$ ? ($.a =3D=3D 1)=E2=80=99

For the purposes of the above example, this appears to hold true: if I =
wrap the path query in exists(), @@ returns a result:

david=3D# select id from movies where movie @@ 'exists($ ?(@.title =3D=3D =
"New Life Rescue"))';
 id
----
 10
(1 row)

Yay! However, @@ and @? don=E2=80=99t seem to use an index the same way: =
@@ uses a GIN index while @? does not.

Or, no, fiddling with it again just now, I think I have still been =
confusing these operators! @@ was using the index with an an explicit =
exists(), but @? was not=E2=80=A6because I was still using an explicit =
exists.

In other words:

* @@ 'exists($ ?($.year =3D=3D 1944))'  Uses the index
* @? '$ ?(@.year =3D=3D 1944)'          Uses the index
* @? 'exists($ ?($.year =3D=3D 1944))'  Does not use the index

That last one presumably doesn=E2=80=99t work, because there is an =
implicit exists() around the exists(), making it `exists(exists($ =
?($.year =3D=3D 1944)))`, which returns true for every row  (true and =
false both exists)! =F0=9F=A4=A6=F0=9F=8F=BB=E2=80=8D=E2=99=82=EF=B8=8F.

Anyway, if I have this right, I=E2=80=99d like to flesh out the docs a =
bit.

Issue 3: Index Use for Comparison
---------------------------------

=46rom the docs =
(https://www.postgresql.org/docs/current/datatype-json.html#JSON-INDEXING)=
, I had assumed any JSON Path query would be able to use the GIN index. =
However while the use of the =3D=3D JSON Path operator is able to take =
advantage of the GIN index, apparently the >=3D operator cannot:

david=3D# explain analyze select id from movies where movie @? '$ =
?($.year >=3D 2023)';
                                               QUERY PLAN                =
                                 =
--------------------------------------------------------------------------=
-------------------------------
 Seq Scan on movies  (cost=3D0.00..3741.41 rows=3D366 width=3D4) (actual =
time=3D34.815..36.259 rows=3D192 loops=3D1)
   Filter: (movie @? '$?($."year" >=3D 2023)'::jsonpath)
   Rows Removed by Filter: 36081
 Planning Time: 1.864 ms
 Execution Time: 36.338 ms
(5 rows)

Is this expected? Originally I tried with json_path_ops, which I can =
understand not working, since it stores hashes of paths, which would =
allow only exact matches. But a plain old GIN index doesn=E2=80=99t =
appear to work, either. Should it? Is there perhaps some other op class =
that would allow it to work? Or would I have to create a separate BTREE =
index on `movie -> 'year'`?

Thanks your your patience with my questions!

Best,

David


--Apple-Mail=_9A554064-C8C0-446D-B373-CF39ABB9123A
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+KfmbOQis6ZycGGkt9idPjIgfEFAmUA/3UACgkQkt9idPjI
gfHFpBAAsQo8p6ldCNXWx9+yQhBr77cI7Zf+xnH0BMv48cDiVKfhMl2n6uQ6DRHj
WrZwG6yz+J14injE2FBLI8OlH9aUvb72AvA2Xdyv9BBme/w1yq1O0D/Eqd2Px0B7
kaBzu2c7VLMyl30qOcbF2tb2s5Bl+4pu/fPAWHIzcSyKPmQ5docCrGfsfJe8JhZq
k3lZwTARiySVS+PsOr9NqW4uXyLKRYZ0520ep67d7w2K68JAGVtqUd4MqwuCQqRJ
RaWuJOoAsbTGxYYhT8nBh2S67XIWC78lBlspwJRIgT9eXuWzb+5SECDtamoL7mcZ
7Qjw89BVXbX+z60A/YkC52VufyeIXXBcCARBH1nMTFR+uH0mSx/qFQbeOuTzuiri
m4WcBbBQ4P6t3wttW39Ak+vgSAKYo1TUA/+Bz/H484tCYpL+BXJ3P32GQ4sAT8CK
cKU/ZSOahPpHv78Zuwp03JMm0lOIN9Gh6k6kqRY5xgMDGU7PUGZtkzYQthJ1vw2a
tNt0b1hlBacdXptmdLw+6nAxogAaCy2GF3qc9L2cywWuClNN6kOvSnFXRtAdO1vU
7Y5xF42GUV+JfkhTT6cu/RKqAWGFNV1FBnLOcn4eK2W9Rbw9zK/nGKIzy9u07Yup
stq9Hg7hicNTByzGHXmpJGGxRVrICy1XYv73hVsJX0p9rJZ78xs=
=ZFP5
-----END PGP SIGNATURE-----

--Apple-Mail=_9A554064-C8C0-446D-B373-CF39ABB9123A--