Can PostgreSQL index deeper paths into JSON columns?

Question

I'm preparing to use PostgreSQL's json storage and querying functionality. I think I understand the insert and query part somewhat, but I cannot find an example of how to support (through an index) a query into json path that is more than one level deep. I'm testing with the following:

CREATE TABLE orders (
    id serial NOT NULL PRIMARY KEY,
    info json NOT NULL
);

INSERT INTO orders (info)
VALUES('{ "customer": "Mark Stevens", "items": {"product": { "name" : "Phone","qty": 3}}}'),
      ('{ "customer": "Josh William", "items": {"product": { "name" : "Toy Car","qty": 1}}}'),
      ('{ "customer": "Mary Clark", "items": {"product": { "name" : "Toy Train","qty": 2}}}');

It's a bit of a constructed example. The essence is that the qty field is nested more than one level deep.

I can execute a query on this looking for all records with qty 1:

SELECT * FROM orders
WHERE info::jsonb @@ '$.items.product.qty == 1';

This all works fine. Tested using PgAdmin. So, now I want to define an index to support that query (or a version of it that can be supported by an index, as often the way you write the query matters).

I have been looking around here, and in the pg documentation, but did not see an example of an index definition that I could turn into a working one for this example. All examples seem to cover paths of only one level. for example, if qty had been one level deep into the json, the supporting index would have looked something like this

CREATE INDEX orders_index ON orders (((info ->> 'customer')::VARCHAR), ((info #>> '{items, qty}')::INTEGER));

So, my question is: Is it possible to come up with an index supporting my query into deeper json levels? And if yes, can someone provide me with an example?

postgresql.org/docs/current/datatype-json.html#JSON-INDEXING — Bergi
– Bergi, Commented Aug 21, 2020 at 14:46

score 4 · Accepted Answer · 2020-08-21 14:56:40Z

4

You should define the column jsonb, there is no advantage in using json here. The following assumes a jsonb column, not a json column

You can define a GIN index on the column:

create index on orders using gin (info);

Or alternatively, for a slightly smaller and more efficient index:

create index on orders using gin (info jsonb_path_ops);

Then the query:

SELECT * 
FROM orders
WHERE info @@ '$.items.product.qty == 1';

can make of that index - with the usual restrictions. If you only have a few hundred rows, this probably isn't going to be used.

The alternative query:

SELECT * 
FROM orders
WHERE info @> '{"items": {"product" : {"qty": 1}}}'

would also use that index.

You can create a smaller (GIN) index if you know that you will always look into e.g. the products part:

create index on orders using gin ( (info #> '{items,products}') );

But then you need to adjust your query:

SELECT * 
FROM orders
WHERE info #> '{items,products}' @@ '$.qty == 1';

You can see the supported JSONB operators here

If you always want to query for the quantity, then maybe a B-Tree index is sufficient. B-Tree indexes are smaller than GIN indexes and also have less overhead to keep them up-to-date.

create index on orders ( ((info #>> '{items,product,qty}')::int) );

Then the following query would use that index:

SELECT * 
FROM orders
WHERE (info #>> '{items,product,qty})::int = 1;

edited Aug 21, 2020 at 14:56

answered Aug 21, 2020 at 14:51

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

Mark Over a year ago

Thank you! The b-tree answer is what I was looking for, in combination with the jsonb type and having sufficient records in the table to have pg consider using the index at all. I have my test working now.

Collectives™ on Stack Overflow

Can PostgreSQL index deeper paths into JSON columns?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related