Update The Value Of An Attribute In A Postgresql Jsonb Array Of Objects Across Muliple Records

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 postgr

Solution 1:

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

Demo

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

UPDATE 1:

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

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

UPDATEgroupsAS g
   SET grp = REPLACE(grp::text, '"tit": "group 1 "','"tit": "newTitle Group 1 "')::json
 WHERE g.name IN ( SELECT g.name 
                     FROMgroupsAS g 
                    CROSSJOIN jsonb_array_elements(grp) AS j 
                    WHERE j.value->>'ona'='joe' ) 

Demo

UPDATE 2:

If you want to find the desired value(gid=1in 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
   FROMgroupsAS g2 
  CROSSJOIN jsonb_array_elements(grp) 
   WITH ORDINALITY arr(j,index)
  WHERE j->>'gid'='1' 
)
UPDATEgroupsAS g
   SET grp = jsonb_set(grp,t.path,'"newTitle Group 1 "',false)
  FROM t

Demo