如何更新嵌套json Postgres中的值

Question

我在"信息"；列

{
"customConfig": {
"isCustomGoods": 1
},
"new_addfields": {
"data": [
{
"val": {
"items": [
{
"Code": "calorie",
"Value": "365.76"
},
{
"Code": "protein",
"Value": "29.02"
},
{
"Code": "fat",
"Value": "23.55"
},
{
"Code": "carbohydrate",
"Value": "6.02"
},
{
"Code": "spirit",
"Value": "1.95"
}
],
"storageConditions": "",
"outQuantity": "100"
},
"parameterType": "Nutrition",
"name": "00000000-0000-0000-0000-000000000001",
"label": "1"
},
{
"name": "b4589168-5235-4ec5-bcc7-07d4431d14d6_Для ресторанов",
"val": "true"
}
]
}
}

我想更新嵌套json 的值

{
"name": "b4589168-5235-4ec5-bcc7-07d4431d14d6_Для ресторанов",
"val": "true"
}

并设置"；val"；至"；是"；str，所以结果应该像一样

{
"name": "b4589168-5235-4ec5-bcc7-07d4431d14d6_Для ресторанов",
"val": "Yes"
}

我该怎么做？假设我需要为数据库中的许多记录更新json中的这个值

Answer 1

我们可以使用Postgres 9.5+提供的jsonb_set()

来自文档：

jsonb_set(target jsonb, path text[], new_value jsonb [, create_missing boolean])

查询以更新嵌套对象：

UPDATE temp t
SET info = jsonb_set(t.info,'{new_addfields,data,1,val}', jsonb '"Yes"')
where id = 1;

它也可以用于选择查询：

SELECT 
jsonb_set(t.info,'{new_addfields,data,1,val}', jsonb '"Yes"')
FROM temp t
LIMIT 1;

Answer 2

考虑到表中有一个常量JSON结构和主键想法是获得具有值true的元素val的精确路径(可以在数组中的任何索引处(，然后将其替换为所需值。所以你可以写下你的查询如下：

with cte as (
select 
id, 
('{new_addfields,data,'||index-1||',val}')::text[] as json_path
from 
test, 
jsonb_array_elements(info->'new_addfields'->'data') 
with ordinality arr(vals,index) 
where 
arr.vals->>'val' ilike 'true'
)
update test 
set info = jsonb_set(info,cte.json_path,'"Yes"',false) 
from cte 
where test.id=cte.id;

演示