How to filter json array per each returned row in Postgresql

Question

Could you please help me in case the object structure is like this,

I have a table with JSON field where an 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', Observation: [Value: 'Value1'] },{ field: 'def', Observation: [Value: 'Value2'] },{ field: 'ghi, Observation: [Value: 'Value3']' }]
id = 2, jsonColumn = [{ field: 'pqr', Observation: [Value: 'Value1'] },{ field: 'aaa', Observation: [Value: 'Value2'] },{ field: 'ccc, Observation: [Value: 'Value3']' }]
id = 3, jsonColumn = [{ field: 'www', Observation: [Value: 'Value1'] },{ field: 'qqq', Observation: [Value: 'Value2'] },{ field: 'rrr', Observation: [Value: 'Value3']' }]

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

Bergi Bergi · Accepted Answer · 2020-02-04T13:58:17

You can use a sub-select and json_array_elements that are then aggregated back to an array:

SELECT
  id,
  ( SELECT json_agg(element)
    FROM json_array_elements(json_column) element
    WHERE … -- e.g. element -> 'field' = …
  ) AS filtered_json_column
FROM
  table

How to filter json array per each returned row in Postgresql

1 Answers