I
I
Ilya Naumov2015-06-26 09:32:36
PostgreSQL
Ilya Naumov, 2015-06-26 09:32:36

How to organize a selection of one of many in a JSONB field using an index?

Good afternoon, there is the following task:
the table has a JSONB field, a GIN index is created for it, product attributes are stored in this field: {"attrName1":"value1", "attrName2":"value2"}the number of attributes and their names are obviously unknown.
It is necessary to organize a filter by attributes, the user selects several values ​​for an arbitrary attribute with checkmarks attrName1("value1", "value2", "value3",...).
Now the actual question is how to correctly compose a query so that the selection takes place taking into account the indices?

... WHERE attributes->>'attrName1' IN ("value1","value2",...)
- searches without indexes, if you add it attributes->>'attrName1'to the index, it searches with indexes, but I don’t know in advance what attributes will be,
...WHERE attributes @> '{"attrName1":"value1"}'- searches with indexes but only for one value.
I’ve been working with PostgreSQL quite recently, but I didn’t find anything in the documentation on my question, or maybe I didn’t search well ...

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question