Search code examples
sqlarraysjsonpostgresqljsonb

Check if Postgres jsonb contains one of the values

I need to check if jsonb field that can be an array or just string. Simple data that I have:

INSERT INTO users (jsonb) VALUES
('{"name":"Henry", "favoritefood_1":["eggs","apples"]}'),
('{"name":"Herald","favoritefood_1":["apples","potatoes"]}'),
('{"name":"Helen", "favoritefood_1":"apples"}');

How should I find users that love apples and eggs? I tried many queries and the last of them is:

SELECT * FROM users
WHERE CASE
WHEN jsonb_typeof((jsonb->>'favoritefood_1')::jsonb) = 'array'
THEN jsonb_array_elements((jsonb->>'favoritefood_1')::jsonb) IN ('apples', 'eggs')
ELSE (jsonb->>'favoritefood_1')::jsonb IN ('apples', 'eggs') END

Can anybody help me with that, please?

Solution 
  • where jsonb -> 'favoritefood_1' ?| array['apples', 'eggs']