删除特定的键和值在PostgreSQL列嵌套的json数组

Question

我有一个json对象字段在PostgreSQL在mytable。字段如下

{
"a": "1",
"b": [
{
"c": 2,
"d": 3
},
{
"c": 4,
"d": 5
},
{
"c": 6,
"d": 7
}
]
}

如何删除所有"c"键和值对如下所示?我找到了其他的StackOverflow解决方案，可以删除整个对象，但我找不到这个。

{
"a": "1",
"b": [
{
"d": 3
},
{
"d": 5
},
{
"d": 7
}
]
}

Answer 1

with data as (
select 
your_json_col :: jsonb as obj,
jsonb_array_elements((your_json_col ->> 'b') :: jsonb) :: jsonb - 'c' as new_obj
from your_table)
select 
obj || jsonb_build_object('b', jsonb_agg(new_obj))
from data
group by obj;