如何在Postgres JSON字段的对象数组中更新多个对象的特定值

Question

这是我的JSON字段，其中有多个用户具有相同的名称。我想更新所有名字为的用户Devang到Dev

JSON

{
"user": [
{
"user_name": "Devang",
"user_weight": 0.7676846955248864
},
{
"user_name": "Meet",
"user_weight": 0.07447325861051013
},
{
"user_name": "Devang",
"user_weight": 0.056163873153859706
}
],
"address": [
{
"address_name": "India"
}
]
}

更新后的JSON将是

{
"user": [
{
"user_name": "Dev",
"user_weight": 0.7676846955248864
},
{
"user_name": "Meet",
"user_weight": 0.07447325861051013
},
{
"user_name": "Dev",
"user_weight": 0.056163873153859706
}
],
"address": [
{
"address_name": "India"
}
]
}

在这里，我已经尝试了这个查询，但由于子查询，只更新了第一个出现。

with cte as (
select  id,  ('{user,'||index-1||',user_name}')::text[] as json_path
from user_table, jsonb_array_elements(json_field->'user') 
with ordinality arr(vals,index) where arr.vals->>'user_name' ='Devang'
)
update user_table 
set json_field = jsonb_set(json_field,cte.json_path,'"Dev"',false) 
from cte where user_table.id=cte.id;

请也看看这个DEMO

欢迎回答

Answer 1

您可以使用字符串函数REPLACE:

UPDATE user_table
SET json_field = REPLACE(json_field :: TEXT, '"user_name": "Devang"', '"user_name": "Dev"') :: JSONB;

https://dbfiddle.uk/?rdbms=postgres_10&小提琴= fa36275977f85a1233bcbec150ada266