4

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?

2

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/

0

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

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

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'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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