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 FUNCTION jsonb_replace_by_key(obj jsonb, search text, substitute jsonb) RETURNS jsonb
STRICT LANGUAGE SQL AS $$
SELECT CASE jsonb_typeof(obj)
WHEN 'object' THEN
(SELECT jsonb_object_agg(key, jsonb_replace_by_key(value, search, substitute)) || CASE WHEN obj ? search THEN substitute ELSE '{}' END
FROM jsonb_each(obj)
WHERE key <> search)
WHEN 'array' THEN
(SELECT jsonb_agg(jsonb_replace_by_key(el, search, substitute))
FROM jsonb_array_elements(obj) el)
ELSE
obj
END;
$$;
CREATE FUNCTION
SELECT jsonb_pretty(jsonb_replace_by_key('{
"good_key": {
"critical": "info",
"bad_key": {
"worthless": "trash"
}
},
"bad_key": {
"more": "garbage"
}
}'::jsonb, 'bad_key', '{"substitute_key": {"some": "info"}}'))
| jsonb_pretty |
|---|
| { "good_key": { "critical": "info", "substitute_key": { "some": "info" } }, "substitute_key": { "some": "info" } } |
SELECT 1