Best Way to Index All JSONB Fields With Boolean Values in PostgreSQL

Question

I am creating a table that has a features jsonb column. There will be a dynamic set of features (each row can have an unknown set of features). Each feature is boolean true/false values.

Example:

Row 1: feature: { "happy": true, "tall": false, "motivated": true }
Row 2: feature: { "happy": true, "fast": true, "strong": false }
Row 3: feature: { "smart": true, "fast": true, "sleepy": false }

What would be the best way to index this column such that I can make queries to find all rows with featureX = true? All the examples I have looked up seem to need a field name to base the index on.

user330315 · Accepted Answer · 2019-11-08 06:43:47Z

4

You can create an index on the complete JSON value:

create index on the_table using gin (features);

It can be used for e.g. the @> operator:

select *
from the_table
where features @> '{"happy": true}'

Another method would be to not store key/value pairs, but only list the features that are "true" in an array: ["happy", "motivated"] and then use the ? operator. This way the JSON value is a bit smaller and that might be more efficient.

select *
from the_table
where features ? 'happy'

or if you want to test for multiple features:

select *
from the_table
where features ?| array['happy', 'motivated']

That too can make use of the GIN index

answered Nov 8, 2019 at 6:43

user330315

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

3 Comments

jjanes Over a year ago

It might be worth noting that a query on a boolean is very likely to be so unselective that, while the index can theoretically be used, it could be slower than just doing the table scan.

PressingOnAlways Over a year ago

Thanks for the thorough answer! If I am searching for multiple features (which I definitely will), is storing as an array faster to lookup? In the current schema, would it fall back to table scan if I had to do something like where features @> '{"happy": true, "motivated": true}' ?

user330315 Over a year ago

features @> '{"happy": true, "motivated": true}' should make use of the GIN index if it makes sense. Check the execution plan

Collectives™ on Stack Overflow

Best Way to Index All JSONB Fields With Boolean Values in PostgreSQL

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related