Reputation: 4032
I have a database table like the following:
---------------------------------------
| id | json_array (jsonb) |
---------------------------------------
| 1 | [{"a": 1}, {"a": 5}, {"a": 1}] |
| 2 | [{"a": 2}] |
| 3 | [{"a": 1}] |
---------------------------------------
I want to use PostgreSQL's JSON query abilities to select certain sub dictionaries in the json_array
array, e.g. dictionaries where "a": 1
.
The output should be
------------------
| id | json_dict |
------------------
| 1 | {"a": 1} |
| 1 | {"a": 1} |
| 3 | {"a": 1} |
------------------
The following query works for the first element in each array, but I want to check for all of them:
SELECT id, json_array -> 0
FROM my_table
WHERE json_array -> 0 -> "a" = 1;
Upvotes: 7
Views: 18538
Reputation:
Assuming this is a JSONB column, you can use the @>
operator with a json object:
select *
from my_table
where json_array @> '[{"a": 1}]';
Online example: https://rextester.com/SACUDU51598
If you want all objects as rows, you need to unnest the array:
select t.id, e.obj
from data t
cross join jsonb_array_elements(json_array) as e(obj)
where e.obj = '{"a": 1}'
Online example: https://rextester.com/DLG27322
Upvotes: 9
Reputation: 23756
You can expand your array elements into one element each row with jsonb_array_elements()
. This can be filtered:
SELECT
id,
elems.value
FROM
mytable,
jsonb_array_elements(data) elems
WHERE
elems.value = '{"a":1}'
Upvotes: 5