Indexing object element inside json array with Postgres

Question

I have a 9.4 Postgres database which has a table containing a jsonb field.

The structure of the json looks similar to this:

{
  name: 'customer1',
  age: 28,
  products: [{
    name: 'product1',
    price: 100
  }, {
    name: 'product2',
    price: 200
  }]
}

The following query returns the aforementioned json just fine:

SELECT jdoc
FROM customers, jsonb_array_elements(jdoc->'products') as products
WHERE (products->>'price')::numeric > 150

The problem is that the performance suffers quite a bit on bigger databases.

What index can I use to speed up this query?

What I've tried to far:

GIN indices (both jsonb_ops and jsonb_path_ops). They only seem to work on existence operators like @> though.
CREATE INDEX ON persons(((jsonb_array_elements(jdoc->'products')->>'price')::numeric)). Which give me give me datatype mismatch error.
- Note that CREATE INDEX ON persons(((jdoc->'age')::numeric)) works and allows me to query (jdoc->>'age')::numeric < 30 fast.

Use relational data model instead of JSON.

Abelisto
– Abelisto

2016-12-15 13:37:28 +00:00
Commented Dec 15, 2016 at 13:37 — Abelisto
– Abelisto, Commented Dec 15, 2016 at 13:37

Laurenz Albe · Accepted Answer · 2016-12-15 13:51:36Z

4

You could create a function and use that:

CREATE FUNCTION max_price(jsonb) RETURNS double precision
   LANGUAGE sql IMMUTABLE AS
$$SELECT max(p.price)
FROM jsonb_to_recordset($1->'products')
        AS p(name text, price double precision)$$;

CREATE INDEX customers_ind ON customers(max_price(jdoc));

Then this index can be used with a query like this:

EXPLAIN SELECT jdoc FROM customers WHERE max_price(jdoc) > 150;

                                   QUERY PLAN
--------------------------------------------------------------------------------
 Index Scan using customers_ind on customers  (cost=0.12..8.14 rows=1 width=36)
   Index Cond: (max_price(jdoc) > '150'::double precision)
(2 rows)

This is not your query, but it should be equivalent.

answered Dec 15, 2016 at 13:51

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

3 Comments

Kabbalah Over a year ago

But this means I also have to create a second function/index that uses min(p.price) if I want to query for a price less than a value. Am I right?

Laurenz Albe Over a year ago

Yes. This targets just the special case in your question. The answer should give you the idea how a problem like yours might be tackled.

Kabbalah Over a year ago

Thank you. I'll wait a day or two to see if there are other options before accepting.

Collectives™ on Stack Overflow

Indexing object element inside json array with Postgres

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related