Indexing and querying int value from JSON array in PostgreSQL

Question

I want to create a table with a column containing jsonb data-type. What would be the proper way to index and query a int value in an array?

Entry:

{
  "list": [
    {
      "type": "FOO",
      "value": 1000
    },
    {
      "type": "BAR",
      "value": 200
    }
  ]
}

2 different queries, I want to find all entries which contain a list-item,

Type = 'FOO' AND value > 500
value > 100

What version of Postgres are you using?

Tim Biegeleisen
– Tim Biegeleisen

2019-02-27 10:18:43 +00:00
Commented Feb 27, 2019 at 10:18 — Tim Biegeleisen
– Tim Biegeleisen, Commented Feb 27, 2019 at 10:18
@TimBiegeleisen 10+

J2B
– J2B

2019-02-27 11:51:31 +00:00
Commented Feb 27, 2019 at 11:51 — J2B
– J2B, Commented Feb 27, 2019 at 11:51

Laurenz Albe · Accepted Answer · 2019-02-27 11:05:34Z

1

You won't be able to index any of the > comparisons, but the equality condition can be indexed:

CREATE INDEX ON tabname USING gin (jsonbcol jsonb_path_ops);

This can be used by a query like the following:

SELECT id FROM tabname
WHERE jsonbcol @> '{ "list": [ {"type": "FOO"} ] }';

If you plan to use JSON attributes heavily inside the database, you are doing something wrong. Keep the attributes you want to index in regular database columns. In your case, that would be a separate table that links to your table with a foreign key, so that there can be several entries for each row.

answered Feb 27, 2019 at 11:05

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

Collectives™ on Stack Overflow

Indexing and querying int value from JSON array in PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related