How to select a particular element from json array

Question

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
– McNets, Commented Sep 18, 2019 at 13:44

jjanes · Accepted Answer · 2019-09-18 17:25:48Z

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]}';

Parul Singh · Accepted Answer · 2019-09-18 15:27:58Z

IF column is json:

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

If column is jsonb:

 SELECT id FROM notifications n WHERE jsonb_array_elements(n.dest_user_id_arr->'users') = 83;

2 Answers 2