I have a table that contains two columns. The primary key and a column called comments that is a text column but contains a json object. Now, I want to update all properties in all columns where the property has a value, by diving that value by 100. However, this doesn't work, since it just sets these properties to null instead:

do $$
declare
  commentKey text;
  commentKeys text[] := array['test1','test2'];
begin
   FOREACH commentKey IN ARRAY commentKeys
  loop
     UPDATE a
     SET comments = jsonb_set(to_jsonb(comments), ('{'||commentKey||'}')::text[], to_jsonb(round(((to_json(comments) ->> commentKey)::numeric) / 100, 6)))
     WHERE comments::jsonb ->> commentKey is not null;
  end loop;
end;
$$ language plpgsql;

Here's a DB fiddle:

What am I doing wrong?

I have a table that contains two columns. The primary key and a column called comments that is a text column but contains a json object. Now, I want to update all properties in all columns where the property has a value, by diving that value by 100. However, this doesn't work, since it just sets these properties to null instead:

do $$
declare
  commentKey text;
  commentKeys text[] := array['test1','test2'];
begin
   FOREACH commentKey IN ARRAY commentKeys
  loop
     UPDATE a
     SET comments = jsonb_set(to_jsonb(comments), ('{'||commentKey||'}')::text[], to_jsonb(round(((to_json(comments) ->> commentKey)::numeric) / 100, 6)))
     WHERE comments::jsonb ->> commentKey is not null;
  end loop;
end;
$$ language plpgsql;

Here's a DB fiddle: https://dbfiddle.uk/uhEChx-S

What am I doing wrong?

• sql
• json
• database
• postgresql
• jsonb

Share Improve this question Follow edited Nov 19, 2024 at 13:01 SCM asked Nov 19, 2024 at 10:53 SCMSCM 8566 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

You can use jsonb_object_agg to build new JSON objects, where keys that match the contents of commentKeys have their values divided by 100, if the value is not null:

do $$
declare
  commentKey text;
  commentKeys text[] := array['test1','test2'];
begin
   update a set comments = (select jsonb_object_agg(k.key, 
       case when k.value#>>'{}' is not null and k.key::text = any(commentKeys) 
              then (k.value::text::float / 100)::text::jsonb 
            else k.value end) 
       from jsonb_each(comments::jsonb) k);
end;
$$ language plpgsql;

See fiddle