Postgresql: Modify one value inside of a nested json column

Question

I have a json column called "Parameters" in a "Assets" table. The jsons are nested and have the following structure:

{
"params1": {"k": 1.1, "efficiency": 0.7}, 
"params2": {"k": 1.9, "C_max": 100.0, "C_min": 10.0}
}

I would like to update only one value "k" in "params2" and keep the rest of the values unchanged. I tried with the following code:

update "Assets" 
set "Parameters" = "Parameters"::jsonb || '{"params2":{"k":2.2}}'::jsonb
where "Id" = 185;

This results in

{
"params1": {"k": 1.1, "efficiency": 0.7}, 
"params2": {"k": 2.2}
}

So the rest of the parameters in "params2" are dropped which is not desired. What would be a way to modify "k" and leave the rest as it is?

Brady Holt · Accepted Answer · 2023-05-19 07:52:34Z

21

You are looking for jsonb_set() (documentation)

update "Assets" 
  set "Parameters" = jsonb_set("Parameters"::jsonb, '{params2, k}', to_jsonb(2.2))
where ...

Brady Holt

1034 bronze badges

answered Apr 22, 2021 at 8:40

user1822

1 Answer 1