I'm on PG 9.5 and I have a table Visitors(id, data::json)


Visitor(id: 1, data: {name: 'Jack', age: 33, is_user: true })

I'd like to perform queries like

  • Give me all visitors named Jack and age > 25
  • Give me all visitors who are users, but where name is unspecified (key not in json)

The keys inside the data column user-specified and as such are dynamic.

Which index makes the most sense in this situation?

3 Answers 3


Alter your JSON column to be jsonb and try a gin index on the data field like:

create index ginner on Visitor using gin(data);

They can be slow to create and big but will allow arbitrary queries. Here's a demonstration:


  • Does GIN speed up range queries (>, <, >=, <=) or only exact matches (=)? I fear that the current implementation of GIN is only useful for exact matches...
    – collimarco
    Nov 24, 2020 at 19:53
  • Ok, I have found the official documentation about this: GIN supports only equality operators (and not <, >) in the current PG implementation (v13). See also JSON indexing
    – collimarco
    Nov 25, 2020 at 10:44

Give me all visitors named Jack and age > 25

Give me all visitors who are users, but where name is unspecified (key not in json)

I assume you've read the docs. The answer is you can not do this. There are two types of index classes that can work on JSONB using GIST and GIN: the jsonb_path_ops, and jsonb_ops (the default).

CREATE INDEX idxginp ON api USING GIN (jdoc jsonb_path_ops);
CREATE INDEX idxgin ON api USING GIN (jdoc);

Using jsonb_ops I believe you can test for the non-existence of a value. However, in neither of these can you test for anything but arbitrary containment. From the docs on JSONB indexing

The default GIN operator class for jsonb supports queries with top-level key-exists operators ?, ?& and ?| operators and path/value-exists operator @>.

Find out what those operators do for the type in the JSON-function docs

If you need to know if Jack is older than 25. You'll have to create a btree on data->>'age'


Try this for first query

create index IX_1 on Visitor using BTree((data->>'age')::INT)

And this for second query

create index IX_2 on Visitor using BTree((data->>'is_user')::INT, (data->>'Name'))

  • 5
    He said the keys are user-generated so that won't cover new cases.
    – CalZ
    Apr 7, 2017 at 16:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.