Update specific JSONB column in postgres

user_1986039 0
Viewed 38

I have a model called Layout, that has this below entry, here the configurations column is a jsonb

 id: 1,
 configurations:
  [
   {"data"=>{"x"=>664, "y"=>176 }, "layout_id"=>"1", "layout_name"=>"Corner"},
   {"data"=>{"x"=>334, "y"=>268 }, "layout_id"=>"2", "layout_name"=>"Outside"}
  ]

I was trying to update/delete the values in the column configurations, What I was trying to do is based on the key id we need to update the name in the json

For example: I was trying to update the name corner to Ground Floor if the Id is 1

Current Date:

   [
     {"data"=>{"x"=>664, "y"=>176 }, "layout_id"=>"1", "layout_name"=>"Corner"},
     {"data"=>{"x"=>334, "y"=>268 }, "layout_id"=>"2", "layout_name"=>"Outside"}
   ]

New data:

   [
     {"data"=>{"x"=>664, "y"=>176 }, "layout_id"=>"1", "layout_name"=>"Ground Floor"},
     {"data"=>{"x"=>334, "y"=>268 }, "layout_id"=>"2", "layout_name"=>"Outside"}
   ]

With the below query I was trying to update, but that doesn't work it properly

Fiddle

UPDATE layout set configurations = jsonb_set(x1.config, '{layout_name}', 
   'Ground Floor') 
    FROM 
     (select * FROM 
        (                                                                                       
           SELECT jsonb_array_elements(d.configurations) AS config
           FROM 
           layout d
           WHERE jsonb_typeof(d.configurations) = 'array') x
        where x.config ->> 'layout_id' = '1'
     ) x1 where id= 1;
postgresql sql json
1 Answers

You can use a conditional with json_agg:

select (select json_agg(case when (v.value -> 'layout_name')::text = '"Corner"' 
        then v.value::jsonb || '{"layout_name":"Ground Floor"}'::jsonb 
        else v.value end) from jsonb_array_elements(l.configurations) v) 
from layout l where l.id = 1

See fiddle.

To update your table:

update layout set configurations = (select json_agg(case 
           when (v.value -> 'layout_id')::text = '"1"' 
           then v.value::jsonb || '{"layout_name":"Ground Floor Edge"}'::jsonb 
           else v.value end) 
     from jsonb_array_elements(layout.configurations) v) 
where layout.id = 1

See fiddle.

user_73267380
Related