I have a jsonb column called data. It is deeply nested and there's a key which's value is an array of objects:
select data#>>'{foo,bar,baz,qux}' from my_table limit 1;
-------------
?column? | [{"a": 1, "b:": 2}, {"a": 3, "b": 4}, {"a": 5, "b_": 6}]
As you see, there are different forms of "b" key.
My goal is to update all rows with "b:" and "b_" keys and set them to "b".
This answer describes the way to rename an attribute of a json object. You can create a function based on this idea:
create or replace function jsonb_rename_attribute(obj jsonb, old_key text, new_key text)
returns jsonb language sql immutable as $$
select obj - old_key || jsonb_build_object(new_key, obj->old_key)
$$;
and another function to facilitate modifications of elements of a json array:
create or replace function jsonb_rename_attribute_in_array(arr jsonb, old_key text, new_key text)
returns jsonb language sql immutable as $$
select jsonb_agg(
case when value ? old_key
then jsonb_rename_attribute(value, old_key, new_key)
else value end)
from jsonb_array_elements(arr);
$$;
Use the function to update the table:
update my_table
set data =
jsonb_set(
data,
'{foo,bar,baz,qux}',
jsonb_rename_attribute_in_array(
jsonb_rename_attribute_in_array(
data#>'{foo,bar,baz,qux}',
'b:', 'b'),
'b_', 'b')
)
where jsonb_typeof(data#>'{foo,bar,baz,qux}') = 'array';
Test it in db<>fiddle.
Exemplary trigger before insert:
create or replace function before_insert_on_my_table()
returns trigger language plpgsql as $$
begin
if jsonb_typeof(new.data#>'{foo,bar,baz,qux}') = 'array' then
new.data =
jsonb_set(
new.data,
'{foo,bar,baz,qux}',
jsonb_rename_attribute_in_array(
jsonb_rename_attribute_in_array(
new.data#>'{foo,bar,baz,qux}',
'b:', 'b'),
'b_', 'b')
);
end if;
return new;
end $$;
create trigger before_insert_on_my_table
before insert on my_table
for each row execute procedure before_insert_on_my_table();
where clause which may be needed in the update query.jsonb_array_elements() returns value (as consecutive array elements). See the description in the documentation.