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?
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;