如何为每个返回的行过滤json数组? [英] How to filter json array per each returned row?
问题描述
我有一个带有json字段的表,用于存储对象数组。
我想查询该表,并且对于每个返回的行,通过使用某种条件对其进行过滤来仅返回json数组对象的子集。
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.
例如,行:
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中仅包含元素,其中field ='abc'
。我只想过滤此列,而不返回包含数组中特定元素的行...
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...
推荐答案
您可以使用 json_array_elements
取消嵌套JSON, array_agg
过滤后将其嵌套。像这样的东西:
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;
这篇关于如何为每个返回的行过滤json数组?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!