postgresql - 如何过滤每个返回行的 json 数组？

Question

我有一个带有 json 字段的表，其中存储了对象数组。我想查询这个表，并且对于每个返回的行，通过使用某些条件过滤它们，只返回 json 数组对象的子集。

例如对于行:

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' }]

我想选择所有行，并且每行应该在 jsonColumn 中仅包含元素 where field = 'abc'。我只想过滤此列，而不返回数组中包含特定元素的行...

Answer 1

您可以使用 json_array_elements 取消嵌套 JSON，并使用 array_agg 在过滤后将其嵌套回去。像这样:

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;