Postgres - JSONB - Nested Columns - Query Nested Jsonb array column

Solution 1:

Use the ?| operator:

select user_id 
from my_table
where profile -> 'data' -> 'races' ?| array['white', 'african american']

According to the documentation:

jsonb ?| text[] -> boolean

Do any of the strings in the text array exist as top-level keys or array elements?

Solution 2:

tl;dr use the ?| operator.

There's two problems with your query.

->> returns text not jsonb. So you're asking if the text ["white", "asian"] matches white or african american.

You probably did that because otherwise you got type errors trying to use any with JSONB. any wants a Postgres array of things to compare, and it has to be an array of jsonb. We can do that...

select user_id
from user
where profile -> 'data' -> 'races' = ANY(array['"white"', '"african american"']::jsonb[]);

But this has the same problem as before, it's checking if the json array [ "white", "asian" ] equals "white" or "african american".

You need an operator which will match against each element of the JSON. Use the ?| operator.

select user_id
from users
where profile -> 'data' -> 'races' ?| array['white', 'african american'];