jsonb 查询,使用数组字段查询JSONB

If I have a jsonb column called value with fields such as:

{"id": "5e367554-bf4e-4057-8089-a3a43c9470c0",

"tags": ["principal", "reversal", "interest"],,, etc}

how would I find all the records containing given tags, e.g:

if given: ["reversal", "interest"]

it should find all records with either "reversal" or "interest" or both.

My experimentation got me to this abomination so far:

select value from account_balance_updated

where value @> '{}' :: jsonb and value->>'tags' LIKE '%"principal"%';

of course this is completely wrong and inefficient

解决方案

As it turned out you can use cool jsonb operators described here:

so original query doesn't have to change much:

select value from account_balance_updated

where value @> '{}' :: jsonb and value->'tags' ?| array['reversal', 'interest'];

in my case I also needed to escape the ? (??|) because I am using so called "prepared statement" where you pass query string and parameters to jdbc and question marks are like placeholders for params: