Select rows in postgres where JSONB field in provided list

Question

Say I have table in Postgres with column data of type JSONB. This column contains pretty complex object for example:

{
  ...,
  gender: ['men', 'women'],
  ...
}

I have query like gender=men&gender=women&gender=something_else and want to find all rows in table where ANY of gender's members `IN ('men', 'women', 'something_else'). For example:

SELECT uuid, data ->> 'gender' FROM "OX_Articles" WHERE data ->> 'gender' INTERSECTS WITH (men', 'women', 'something_else');

Of course we haven't keywords INTERSECTS WITH.

Correct, we don't have that syntax. You should read the docs to see what we do have. postgresql.org/docs/current/functions-json.html — jjanes
– jjanes, Commented Mar 19, 2020 at 14:24

Masklinn · Accepted Answer · 2020-03-19 08:29:31Z

1

Either in (...) or = any(array[...]) should work.

They should have similar performances. I favor =any because it handles empty RHS (whereas IN can't handle empty literal tuples) and I'd expect whatever postgres bindings I have to convert the host language's arrays/lists/arraylists to pg arrays, not pg tuples.

answered Mar 19, 2020 at 8:29

Masklinn

43.7k4 gold badges58 silver badges78 bronze badges

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

Collectives™ on Stack Overflow

Select rows in postgres where JSONB field in provided list

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related