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

klin · Accepted Answer · 2022-09-27T12:47:15.007

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

One question: adding this function to my schema and now when new row will appear, will this function automatically work (updating "bad" keys)? Or is there a need for a trigger or something? — Andrey Deineko, Sep 06 '18 at 11:12
The function isn't called automatically but you can place it in a trigger. — klin, Sep 06 '18 at 11:14
Could you please show the best way to use above with trigger on insert? — Andrey Deineko, Sep 06 '18 at 11:19
See the updated answer. Note also that I've added the `where` clause which may be needed in the update query. — klin, Sep 06 '18 at 11:28
what's `value` in `jsonb_rename_attribute_in_array` function? — Andrey Deineko, Sep 06 '18 at 16:05
`jsonb_array_elements()` returns `value` (as consecutive array elements). See the description in [the documentation.](https://www.postgresql.org/docs/10/static/functions-json.html) — klin, Sep 06 '18 at 16:10

Update key of object inside array of objects in jsonb column

1 Answers1