Reputation: 21
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...
Upvotes: 1
Views: 1750
Reputation: 665574
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
Upvotes: 3