CODEWITHSUNDEEP
jsonpostgresqlindexing
Mark
Mark

Reputation: 1

Can PostgreSQL index deeper paths into JSON columns?

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?

Upvotes: 0

Views: 848

Answers (1)

user330315
user330315

Reputation:

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;

Upvotes: 4

Related Questions