Using GIN Index to see if a nested key exists in JSONB

From: Mike Jarmy <mikej(at)novo(dot)cloud>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Using GIN Index to see if a nested key exists in JSONB
Date: 2024-05-07 21:04:27
Message-ID: CAMLuOiZw+H4r95Qrgv2ZTeC7FK0mmogf7j5X0YJF2LPm82g94A@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have a table of semi-structured json that I am storing in a JSONB column
with
a GIN index:

create table foo (
id text primary key,
obj jsonb
);
create index foo_obj on foo using gin (obj);

I populated the table with 10,000 rows of randomly generated JSON objects,
with
random amounts of nesting. Here are the first few rows:

{"b": 0, "c": 3, "d": 2, "id": "0"}
{"b": 4, "c": {"a": 3, "b": 4, "c": 4, "d": 1}, "id": "1"}
{"a": 3, "b": 1, "c": 0, "d": 0, "id": "2"}
{"a": 3, "b": 2, "c": 4, "d": 1, "id": "3"}
{"a": 2, "b": {"b": 3, "c": 2, "d": 0}, "c": 3, "id": "4"}
{"c": 2, "id": "5"}
{"b": {"a": {"d": 3}, "b": 2, "c": 3, "d": 3}, "id": "6"}
{"a": 0, "b": 1, "id": "7"}
{"a": 1, "c": 3, "d": 3, "id": "8"}
{"a": 4, "b": 3, "c": 1, "d": 1, "id": "9"}
{"a": 0, "b": {"a": 3, "b": 0, "d": 3}, "d": 0, "id": "10"}
{"a": {"b": 2}, "b": 4, "c": 4, "d": 4, "id": "11"}
{"a": {"a": 2, "b": 3, "c": 1, "d": {"c": 4}}, "c": 1, "d": 0, "id":
"12"}
...

There are two queries that I want to issue using the GIN index. The first
query, which seems to work, returns the 2 records in the test data set that
happen to have a nested key with a certain value. If I use EXPLAIN on the
query,
it shows me that the index is being used.

postgres=# explain analyze select obj from foo where (obj @@ '$.a.a.a.a
== 1'::jsonpath);

QUERY PLAN

--------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on foo (cost=44.01..48.02 rows=1 width=102) (actual
time=3.531..5.402 rows=2 loops=1)
Recheck Cond: (obj @@ '($."a"."a"."a"."a" == 1)'::jsonpath)
Rows Removed by Index Recheck: 4211
Heap Blocks: exact=173
-> Bitmap Index Scan on foo_obj (cost=0.00..44.01 rows=1 width=0)
(actual time=2.330..2.331 rows=4213 loops=1)
Index Cond: (obj @@ '($."a"."a"."a"."a" == 1)'::jsonpath)
Planning Time: 0.276 ms
Execution Time: 5.464 ms
(8 rows)

The second query is the one I'm having trouble with. For this one, I just
want
to know if a nested key is present in the row -- I don't care what its
value is.
This query works in the sense that it does return the 13 rows out of 10,000
that
happen to have the given nested key. However, EXPLAIN shows that the index
isn't being used.

postgres=# explain analyze select obj from foo where (obj @?
'$.a.a.a.a'::jsonpath);

QUERY PLAN

--------------------------------------------------------------------------------------------------
Seq Scan on foo (cost=0.00..298.00 rows=1 width=102) (actual
time=0.549..4.360 rows=13 loops=1)
Filter: (obj @? '$."a"."a"."a"."a"'::jsonpath)
Rows Removed by Filter: 9987
Planning Time: 0.164 ms
Execution Time: 4.384 ms

How can I fix the second query so it uses the GIN index? Maybe the first
query
is inefficient too and I just don't understand what EXPLAIN is telling me?
Is
there anything else I'm doing wrong or have muddled up? I'm new to JSONB.

P.S. For what its worth, I've come up with a variant of the first query
that also
works, and that also uses the index:

select obj from foo where (obj @> '{"a": {"a": {"a": {"a":
1}}}}'::jsonb);

Browse pgsql-general by date

  From Date Subject
Next Message buggedme 2024-05-08 00:20:17 Need help migrating MSSQL2008R2 tables into PGSQL & make PGSQL mimic MSSQL behaviour.
Previous Message Greg Sabino Mullane 2024-05-07 19:47:14 Re: Forcing INTERVAL days display, even if the interval is less than one day