Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 482
ChrisJ Avatar asked Oct 25 '16 05:10

ChrisJ


1 Answers

I found the answer tucked away in this SO question.

SELECT * FROM people WHERE people.data->'tags' ?| ARRAY['enemy','frenemy']
like image 94
ChrisJ Avatar answered Sep 19 '22 12:09

ChrisJ