Update key of object inside array of objects in jsonb column

Question

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".

Answer 1

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();