How to filter json array per each returned row?

Question

I have a table with json field where array of objects is stored. I would like to query this table and for each returned row return only subset of json array objects by filtering them using some condition.

For example for rows:

id = 1, jsonColumn = [{ field: 'abc' },{ field: 'def' },{ field: 'ghi' }]
id = 2, jsonColumn = [{ field: 'abc' },{ field: '123' },{ field: '456' }]
id = 3, jsonColumn = [{ field: 'abc' },{ field: '789' },{ field: 'XXX' }]

I would like to select ALL rows, and each row should contain in jsonColumn only elements where field = 'abc'. I just want to filter this column, and NOT return rows that contain specific element in array...

Answer 1

You can use json_array_elements to unnest JSON and array_agg to nest it back after filtering. Something like this:

SELECT t.id, array_to_json(array_agg(j))
FROM your_table t, json_array_elements(t.jsonColumn) j
WHERE j->>'field' = 'abc'
GROUP BY id;