在多个记录中更新对象的PostgreSQL jsonb数组中的属性值 [英] Update the value of an attribute in a postgresql jsonb array of objects across muliple records

问题描述

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

I am trying to update a value tit in three database records that is part of a jsonb array object to change the title of gid 1 to 'newTitle Group 1' from just 'group 1' using postgresql and python.

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"}])

我还没有找到一种方法，请帮忙.似乎大多数postgresql 9.5功能都适用于jsonb对象，而不适用于对象数组.

I have not found a way to do it, please help. Seems like most of the postgresql 9.5 capabilities are for jsonb objects but not arrays of objects.

我尝试在网络上使用许多示例，但所有示例似乎都失败了.我还考虑在实际查询中使用where子句语句，以限制正在搜索的jsonb记录，例如包括WHERE name IN ('harry', 'moe')，但现在仅需要查看此基本更新问题是如何解决的.

I tried to use many examples on the web but all seemed to fail. I also would consider using a where clause statement on the actual query to limit the jsonb records being searched by for example including WHERE name IN ('harry', 'moe') but for now just need to see how this basic update issue is solved.

推荐答案

只需使用REPLACE()函数，包括 :: text 和 :: jsonb 转换，数据库，无需在python代码中进行任何额外的操作:

Just need to use REPLACE() function including ::text and ::jsonb conversions within the DB, without need of any extra operation in the python code :

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

演示

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

Of course, it's possibile to add a where condition such as WHERE name IN ('harry', 'moe') to restrict the update.

更新1:

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

If you need to perform the update for a spesific record within the jsonb object such as

WHERE j->>'ona' = 'joe'，然后在语句中使用jsonb_array_elements()函数:

WHERE j->>'ona' = 'joe', then use jsonb_array_elements() function within your statement as :

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()函数用作:

If you want to find the desired value(gid=1 in this case) within the jsonb column dynamically in order to derive the path, then it's possible to use jsonb_set() function as :

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

演示

这篇关于在多个记录中更新对象的PostgreSQL jsonb数组中的属性值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！