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?.
select jsonb_set(coalesce(properties, '{}'), '{token_approval}', to_jsonb('zoo'::text)) as res
from (values
('{"foo": "bar"}'::jsonb),
('{"foo": "bar", "token_approval": "baz" }'::jsonb),
(null::jsonb)
) t(properties)
| res |
|---|
| {"foo": "bar", "token_approval": "zoo"} |
| {"foo": "bar", "token_approval": "zoo"} |
| {"token_approval": "zoo"} |
SELECT 3
select coalesce(properties, '{}') - 'token_approval' || jsonb_build_object('token_approval', 'zoo') as res
from (values
('{"foo": "bar"}'::jsonb),
('{"foo": "bar", "token_approval": "baz" }'::jsonb),
(null::jsonb)
) t(properties)
| res |
|---|
| {"foo": "bar", "token_approval": "zoo"} |
| {"foo": "bar", "token_approval": "zoo"} |
| {"token_approval": "zoo"} |
SELECT 3