CODEWITHSUNDEEP
sqljsonpostgresqljsonb
knub
knub

Reputation: 4032

PostgreSQL: Query elements in a JSON array

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

Answers (2)

user330315
user330315

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

S-Man
S-Man

Reputation: 23756

demo:db<>fiddle

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

Related Questions