Answer the question
In order to leave comments, you need to log in
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. Answer the question
In order to leave comments, you need to log in
Didn't find what you were looking for?
Ask your questionAsk a Question
731 491 924 answers to any question