Search code examples
jsonpostgresqljsonb

Postgres select where jsonb array contains one or more elements in another array

I've got a postgres column 'data' which is jsonb in this format:

{
  'tags': ['friend','enemy','frenemy']
  ... // other data
}

Say I want to select all rows that are tagged either 'enemy' or 'frenemy', how can i do that?

I know I can select on a single tag with 

SELECT * FROM people WHERE people.data->'tags' ? 'enemy'

I should be using @> ANY, but can't quite figure out the syntax for the to make it work.

Solution

  • I found the answer tucked away in this SO question.

    SELECT * FROM people WHERE people.data->'tags' ?| ARRAY['enemy','frenemy']