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

Question

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.

Community · Accepted Answer · 2017-04-13 12:42:36Z

12

I found the answer tucked away in this SO question.

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

CommunityBot

11 silver badge

answered Oct 25, 2016 at 6:08

ChrisJ

2,8701 gold badge24 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1