The task is to remove multiple nested keys from jsonb field.
Is there any way to shorten this expression without writing a custom function?
SELECT jsonb '{"a": {"b":1, "c": 2, "d": 3}}' #- '{a,b}' #- '{a,d}';
suppose we need to delete more than 2 keys
There is no way to shorten the expression. If your goal is to pass to the query a single array of keys to be deleted you can use jsonb_set() with jsonb_each():
with my_table(json_col) as (
values
(jsonb '{"a": {"b":1, "c": 2, "d": 3}}')
)
select jsonb_set(json_col, '{a}', jsonb_object_agg(key, value))
from my_table
cross join jsonb_each(json_col->'a')
where key <> all('{b, d}') -- input
group by json_col -- use PK here if exists
jsonb_set
-----------------
{"a": {"c": 2}}
(1 row)
The solution is obviously more expensive but may be handy when dealing with many keys to be deleted.
NVM, figured it out)
For this particular case, we can re-assign property with removed keys (flat):
SELECT jsonb_build_object('a', ('{ "b":1, "c": 2, "d": 3 }' - ARRAY['b','d']));
More general approach:
SELECT json_col || jsonb_build_object('<key>',
((json_col->'<key>') - ARRAY['key-1', 'key-2', 'key-n']));
Not very useful for deep paths, but works ok with 1-level nesting.
