I have a 'notifications' table with a column 'dest_user_id_arr' as json with the following value:

id                     dest_user_id_arr
1                      {"users":[83,84,85]}
2                      {"users":[89,83,92]}
3                      {"users":[87,88,83]}

I would like to select those id where user is 83.

  • Please tag your PostgreSQL version. Is it a JSON or a JSONB column? – McNets Sep 18 '19 at 13:44

If the type is jsonb, Use the jsonb containment operator:

SELECT * FROM notifications n WHERE dest_user_id_arr @> '{"users":[83]}';

If the type is json or text, either change it to jsonb, or (if you don't care about performance) dynamically cast it.

SELECT * FROM notifications n WHERE dest_user_id_arr::jsonb @> '{"users":[83]}';

IF column is json:

  FROM notifications n
  WHERE json_array_elements(n.dest_user_id_arr->'users') = 83;

If column is jsonb:

  FROM notifications n
  WHERE jsonb_array_elements(n.dest_user_id_arr->'users') = 83;
  • "ERROR: set-returning functions are not allowed in WHERE" – jjanes Sep 18 '19 at 17:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.