Subject: Re: JSON Path and GIN Questions

From: Erik Wienhold <[email-masked]>
Date: Wed, 13 Sep 2023 03:00:07 +0200 (CEST)

Hi David,

On 13/09/2023 02:16 CEST David E. Wheeler <[email-masked]> wrote:

> 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;
>
> I have been confused as to the difference between @@ vs @?: Why do these
> return different results?
>
> david=# select id from movies where movie @@ '$ ?(@.title == "New Life Rescue")';
>  id
> ----
> (0 rows)
>
> david=# select id from movies where movie @? '$ ?(@.title == "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?

That's also my understanding.  We had a discussion about the docs on @@, @?, and
jsonb_path_query on -general a while back [1].  Maybe it's useful also.

> If so, I’d 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.

+1

[1] https://www.postgresql.org/message-id/CACJufxE01sxgvtG4QEvRZPzs_roggsZeVvBSGpjM5tzE5hMCLA%40mail.gmail.com

--
Erik

---

From: Erik Rijkers <[email-masked]>
Date: Wed, 13 Sep 2023 07:11:52 +0200

> [1] https://www.postgresql.org/message-id/CACJufxE01sxgvtG4QEvRZPzs_roggsZeVvBSGpjM5tzE5hMCLA%40mail.gmail.com

"All use of json*() functions preclude index usage."

That sentence is missing from the documentation.

Erik Rijkers

---

From: "David E. Wheeler" <[email-masked]>
Date: Wed, 13 Sep 2023 16:01:03 -0400

> That sentence is missing from the documentation.

Where did that come from? Why wouldn’t JSON* functions use indexes? I see that the docs only mention operators; why would the corresponding functions behave the same?

D

---

From: "David E. Wheeler" <[email-masked]>
Date: Fri, 15 Sep 2023 16:27:37 -0400

> jsonb_path_query on -general a while back [1].  Maybe it's useful also.

Okay, I’ll take a pass at expanding the docs on this. I think a little mini-tutorial on these two operators would be useful.

Meanwhile, I’d like to re-up this question about the index qualification of non-equality JSON Path operators.

On Sep 12, 2023, at 20:16, David E. Wheeler <[email-masked]> wrote:

> Issue 3: Index Use for Comparison
> ---------------------------------
> 
> From 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 == JSON Path operator is able to take advantage of the GIN index, apparently the >= operator cannot:
> 
> david=# explain analyze select id from movies where movie @? '$ ?($.year >= 2023)';
>                                               QUERY PLAN                                      ---------------------------------------------------------------------------------------------------------
> Seq Scan on movies  (cost=0.00..3741.41 rows=366 width=4) (actual time=34.815..36.259 rows=192 loops=1)
>   Filter: (movie @? '$?($."year" >= 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’t 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,

David

---

From: Erik Rijkers <[email-masked]>
Date: Sat, 16 Sep 2023 05:59:26 +0200

>> Issue 3: Index Use for Comparison

movie @? '$ ?($.year >= 2023)'

I believe it is indeed not possible to have such a unequality-search use 
the GIN index.  It is another weakness of JSON that can be unexpected to 
those not in the fullness of Knowledge of the manual. Yes, this too 
would be good to explain in the doc where JSON indexes are explained.

Erik Rijkers

---

From: "David E. Wheeler" <[email-masked]>
Date: Sat, 16 Sep 2023 16:19:23 -0400

On Sep 15, 2023, at 23:59, Erik Rijkers <[email-masked]> wrote:

> I believe it is indeed not possible to have such a unequality-search use the GIN index.  It is another weakness of JSON that can be unexpected to those not in the fullness of Knowledge of the manual. Yes, this too would be good to explain in the doc where JSON indexes are explained.

Is that a limitation of GIN indexes in general? Or could there be opclass improvements in the future that would enable such comparisons?

Thanks,

David

---

From: "David E. Wheeler" <[email-masked]>
Date: Sat, 16 Sep 2023 16:26:07 -0400

>> suggesting the use of jsonb_path_query() to test paths to see if they return

I’ve started work on this; there’s so much to learn! Here’s 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=# 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=# 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-SQLJSON-PATH

---

From: Erik Wienhold <[email-masked]>
Date: Sat, 16 Sep 2023 22:50:13 +0200 (CEST)

On 16/09/2023 22:19 CEST David E. Wheeler <[email-masked]> wrote:

> > those not in the fullness of Knowledge of the manual. Yes, this too would
> > be good to explain in the doc where JSON indexes are explained.
>
> Is that a limitation of GIN indexes in general? Or could there be opclass
> improvements in the future that would enable such comparisons?

This detail is mentioned in docs [1]:

"For these operators, a GIN index extracts clauses of the form
 **accessors_chain = constant** out of the jsonpath pattern, and does the
 index search based on the keys and values mentioned in these clauses."

I don't know if this is a general limitation of GIN indexes or just how these
operators are implemented right now.

[1] https://www.postgresql.org/docs/current/datatype-json.html#JSON-INDEXING

--
Erik

---

From: Erik Wienhold <[email-masked]>
Date: Sun, 17 Sep 2023 00:13:56 +0200 (CEST)

On 16/09/2023 22:26 CEST David E. Wheeler <[email-masked]> wrote:

> I’ve started work on this; there’s so much to learn! Here’s 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:
>

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:

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

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.

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

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

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

--
Erik

---

From: "David E. Wheeler" <[email-masked]>
Date: Sat, 16 Sep 2023 19:41:12 -0400

> SELECT jsonb_path_query(:'json', 'strict $.track.segments[*].location ? (@[*] < 14)');

Yes, this is what I expected. It means “select the location array if any of its contents is less that 14.”

I don’t understand why it’s 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’s 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.”

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

> 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