1

My products table has a jsonb column called 'specs'.

This specs hash will often have an array field, let's call it 'spec_options'.

When querying this table, I need to find any product that has certain spec options.

Let's say we have a product with these spec_options:

['b', 'c', 'd']

And the user wants to find products with these spec_options:

['a', 'b', 'c']

The query I need should return find above product.

This where clause worked just fine when the field wasn't an array:

'["a", "b", "c"]'::jsonb ? (specs->>'spec_options')

But now that it's an array, I think I need to use the |? operator, and I can't work out how to get the right side of this operation into the correct format. (I think it needs to be text[] but I'm not sure.)

1

You can use the @> operator.

From the docs:

@> Does the left JSON value contain within it the right value?

So in your case it would look something like this:

select * from products where specs @> '{"spec_options": ["a", "b", "c"]}';

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.