Index on JSON field with dynamic keys

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?

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/

StackExchange Database Administrators Q#153111, answer score: 3