By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
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)
$$;
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);
$$;
create table my_table(id serial primary key, data jsonb);
insert into my_table (data) values
('{"foo": {
"bar": {
"baz": {
"qux": [{"a": 1, "b:": 2}, {"a": 3, "b": 4}, {"a": 5, "b_": 6}]
}
}
}
}');
select data#>>'{foo,bar,baz,qux}' as qux
from my_table;
CREATE FUNCTION
CREATE FUNCTION
CREATE TABLE
INSERT 0 1
| qux |
|---|
| [{"a": 1, "b:": 2}, {"a": 3, "b": 4}, {"a": 5, "b_": 6}] |
SELECT 1
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')
)
returning *;
| id | data |
|---|---|
| 1 | {"foo": {"bar": {"baz": {"qux": [{"a": 1, "b": 2}, {"a": 3, "b": 4}, {"a": 5, "b": 6}]}}}} |
UPDATE 1