Suppose we have a table (named rel_1) with an info field of type JSONB. The content of this field is client specific and may be changed every once in a while, but let's assume it to be of the form {"ki":"vi"} / i=0->N.

Now suppose we want to search for specific rows that have k0="foo" AND k1="bar". From the documentation, One can simply use the containment @> operator like so:

SELECT * FROM rel_1 WHERE info @> '{"k0":"foo", "k1":"bar"}';

While being elegant and working perfectly fine, it can also make use of a GIN index. It's also agnostic to what actual data for info might be. As long as the clients use the same keys for writes and reads, the DB side can completely ignore it and still do a fine job at optimizing requests using the index.

The only problem is that this approach fails if we change the requirement a little bit so that multiple values are allowed for a given key: (k0="foo" OR k0="bar") AND k1="baz".

Is there any way to achieve this while retaining the elegance and efficiency of the first solution?


I see 2 rather straight forward approaches depending on how many values we're looking for.

The first option is to look for a document containing the single valued keys (in this example: where info @> '{"k1":"bar"}'; and wrap that with additional filtering for all the other values: info->>k0 in <the values>

The 2nd option would be to create full documents for the various options, in your example it means creating 2 documents to search by. If the number of values is small them it's not too much of a problem and the index will be used.

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.