How to update a multiple values on a JSONB array using postgreSQL

Question

Today I am trying to update the multiple values of jsonb array on the basis of id key.

I have column uniqueid and jsarray in table "people".

E.g. select * from people

1,  [{"id": 1101, "val": "testing1", "valuom": "", "description": "Desc_test_1"}, 
     {"id": 1105, "val": "testing2", "valuom": "", "description": "Desc_test_2"},
     {"i d": 1108, "val": "testing2", "valuom": "", "description": "Desc_test_3"}]

I need to update the values of Description, val and valuom on the basis of id key.

for e.g update the jsarray column where id= 1101 and uniqueid=1; need o/p as:

  1  , [{"id":1101,"val":"testing5","valuom":"test5", "description":"Desc_test_5"}, 
        {"id":1105,"val":"testing2","valuom":"","description":"Desc_test_2"} ,
        {"id":1108,"val":"testing2","valuom":"","description":"Desc_test_3"}];

The need to update specific elements in an array is a strong indication that de-normalizing the data model was the wrong decision — a_horse_with_no_name

Barbaros Özhan Barbaros Özhan · Accepted Answer · 2020-08-31T14:14:43

You can use such a query containing jsonb_set() function in it :

WITH p AS
(
 SELECT ('{'||idx-1||',val}')::text[] AS path_val,
        ('{'||idx-1||',desc}')::text[] AS path_desc
   FROM people 
  CROSS JOIN jsonb_array_elements(jsarray) 
   WITH ORDINALITY arr(j,idx)
  WHERE (j->>'id')::int=1101 
    AND uniqueid = 1
)
UPDATE people
   SET jsarray = jsonb_set(
                           jsonb_set(jsarray,p.path_val,'"testing5"',false),
                                     p.path_desc,'"Desc_test_5"',false
                 )
  FROM p

and then

WITH p AS
(
 SELECT ('{'||idx-1||',val}')::text[] AS path_val,
        ('{'||idx-1||',desc}')::text[] AS path_desc,
        ('{'||idx-1||',valuom}')::text[] AS path_valuom
   FROM people 
  CROSS JOIN jsonb_array_elements(jsarray) 
   WITH ORDINALITY arr(j,idx)
  WHERE (j->>'id')::int=1105 
    AND uniqueid = 1
)
UPDATE people
   SET jsarray = jsonb_set(
                       jsonb_set(
                                jsonb_set(jsarray,p.path_val,'"testing21"',false),
                                 p.path_desc,'"Desc_test_21"',false),
                                 p.path_valuom,'"test21"',false
                 )
  FROM p

consecutively for each of the related ID value within the JSONB value.

Demo

How to update a multiple values on a JSONB array using postgreSQL

1 Answers