Index on JSON field with dynamic keys

Question

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

Example:

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?

Answer 1

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:

https://blog.codeship.com/unleash-the-power-of-storing-json-in-postgres/

• collimarco: 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: 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

Answer 2

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'

Answer 3

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'))

• CalZ: He said the keys are user-generated so that won't cover new cases.