如何更新postgres数组中的json？
如何更新postgres数组中的json？
提问于 2021-06-09 09:21:08
我已经附加了JSON输入和输出
输入
{
"cap1": [
{
"fe1": [
{
"par1": 0,
"fet1": 6,
"fun1": [
{
"fnd1": [
{
"name": "v1",
"site": [
"w1",
"mb1",
"tb1"
]
}
]
}
]
}
],
"capab1": 2
},
{
"fe1": [
{
"par1": 0,
"fet1": 42,
"fun1": null
},
{
"par1": 42,
"fet1": 43,
"fun1": null
}
],
"capab1": 11
}
]
}我想添加{"par1": 0, "fet1": 44, "fun1": null},{"par1": 0, "fet1": 45, "fun1": null} where "capab1": 11。
输出应为
{
"cap1": [
{
"fe1": [
{
"par1": 0,
"fet1": 6,
"fun1": [
{
"fnd1": [
{
"name": "v1",
"site": [
"w1",
"mb1",
"tb1"
]
}
]
}
]
}
],
"capab1": 2
},
{
"fe1": [
{
"par1": 0,
"fet1": 42,
"fun1": null
},
{
"par1": 42,
"fet1": 43,
"fun1": null
},
{
"par1": 0,
"fet1": 44,
"fun1": null
},
{
"par1": 0,
"fet1": 45,
"fun1": null
}
],
"capab1": 11
}
]
}回答 1
Stack Overflow用户
回答已采纳
发布于 2021-06-09 13:50:37
这有点复杂。您可以使用jsonb_array_elements取消数组的嵌套，并获取要更新的路径。然后使用JSONB_INSERT更新JSON，如下所示:解决方案如下：
with cte as (
select *,
('{cap1,'||index1-1||',fe1,0}')::text[] as json_path
from test,
jsonb_array_elements(col->'cap1') with ordinality arr1 (vals1,index1)
where (vals1->>'capab1')::int=11
)
update test
set col = jsonb_insert(test.col,cte.json_path,'[{"par1": 0, "fet1": 44, "fun1": null},{"par1": 0, "fet1": 45, "fun1": null}]'::jsonb,true)
from cte
where test.id=cte.id页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/67896311
复制相关文章
相似问题
腾讯云开发者
