我有一些jsonb数据,如下所示:
{
"a":[
{"b":1,"c":2,"d":3},
{"b":4,"c":5,"d":6}
],
"g":[
{"b":1,"c":2,"d":3},
{"b":4,"c":5,"d":6}
]
}
我想从嵌套数组的"a"和"g"键中删除c键。是否有单个查询可以执行此操作?
我有一些jsonb数据,如下所示:
{
"a":[
{"b":1,"c":2,"d":3},
{"b":4,"c":5,"d":6}
],
"g":[
{"b":1,"c":2,"d":3},
{"b":4,"c":5,"d":6}
]
}
我想从嵌套数组的"a"和"g"键中删除c键。是否有单个查询可以执行此操作?
SELECT
jsonb_object_agg(key, a) -- 5
FROM (
SELECT
mydata,
key,
jsonb_agg(a_elems.value - 'c') as a -- 3/4
FROM
mytable,
jsonb_each(mydata) elems, -- 1
jsonb_array_elements(elems.value) AS a_elems -- 2
GROUP BY mydata, key -- 4
) s
GROUP BY mydata -- 5
-
运算符来删除元素。jsonb_agg()
重新聚合数组jsonb_object_agg()
使用先前生成的键列和新数组列重建原始JSON对象。jsonb
数据,所以你可能希望将所有这些json_*
函数更改为它们对应的jsonb_*
函数。 - GMB