更新多条记录中对象的postgresql-jsonb数组中的属性值

Question

我正在尝试更新作为jsonb数组对象一部分的三个数据库记录中的值tit，以便使用postgresql和python将gid 1的标题从"Group 1"更改为"newTitle Group 1"。

create table groups (name varchar, grp jsonb)
insert into groups (name, grp) values 
('joe', [{"gid": "1", "ona": "joe", "tit": "group 1 "}, {"gid": "2", "ona": "harry", "tit": "tester1 group 2"}, {"gid": "3", "ona": "moe", "tit": "group 3"}]),
('harry', [{"gid": "1", "ona": "joe", "tit": "group 1 "}, {"gid": "2", "ona": "harry", "tit": "tester1 group 2"}, {"gid": "3", "ona": "moe", "tit": "group 3"}])
('moe' , [{"gid": "1", "ona": "joe", "tit": "group 1 "}, {"gid": "2", "ona": "harry", "tit": "tester1 group 2"}, {"gid": "3", "ona": "moe", "tit": "group 3"}])

我想得到结果：

('joe', [{"gid": "1", "ona": "joe", "tit": "newTitle Group 1'"}, {"gid": "2", "ona": "harry", "tit": "tester1 group 2"}, {"gid": "3", "ona": "moe", "tit": "group 3"}]),
('harry', [{"gid": "1", "ona": "joe", "tit": "newTitle Group 1'"}, {"gid": "2", "ona": "harry", "tit": "tester1 group 2"}, {"gid": "3", "ona": "moe", "tit": "group 3"}])
('moe' , [{"gid": "1", "ona": "joe", "tit": "newTitle Group 1'"}, {"gid": "2", "ona": "harry", "tit": "tester1 group 2"}, {"gid": "3", "ona": "moe", "tit": "group 3"}])

我还没有找到做这件事的方法，请帮忙。似乎大多数postgresql9.5功能都是针对jsonb对象的，而不是对象数组。

我试着在网上举了很多例子，但似乎都失败了。我还考虑在实际查询中使用where子句语句来限制要搜索的jsonb记录，例如包括WHERE name IN ('harry', 'moe')，但现在只需要看看如何解决这个基本的更新问题。

Answer 1

只需要在DB中使用REPLACE()函数，包括：text和:：jsonb转换，而不需要在python代码中进行任何额外操作：

UPDATE groups
SET grp = REPLACE(grp::text, '"tit": "group 1 "','"tit": "newTitle Group 1 "')::jsonb

演示

当然，可以添加where条件(如WHERE name IN ('harry', 'moe')(来限制更新。

更新1:

如果您需要对jsonb对象中的特定记录执行更新如

WHERE j->>'ona' = 'joe'，然后在您的语句中使用jsonb_array_elements()函数作为：

UPDATE groups AS g
SET grp = REPLACE(grp::text, '"tit": "group 1 "','"tit": "newTitle Group 1 "')::json
WHERE g.name IN ( SELECT g.name 
FROM groups AS g 
CROSS JOIN jsonb_array_elements(grp) AS j 
WHERE j.value->>'ona' = 'joe' ) 

演示

更新2:

如果您想在jsonb列中动态找到所需的值(在本例中为gid=1(以导出路径，则可以使用jsonb_set()函数作为：

WITH T AS
(
SELECT ('{'||index-1||',tit}')::text[] AS path
FROM groups AS g2 
CROSS JOIN jsonb_array_elements(grp) 
WITH ORDINALITY arr(j,index)
WHERE j->>'gid'='1' 
)
UPDATE groups AS g
SET grp = jsonb_set(grp,t.path,'"newTitle Group 1 "',false)
FROM t

演示