CODEWITHSUNDEEP
postgresqljsonb
Ihor Sakailiuk
Ihor Sakailiuk

Reputation: 6068

PostgreSQL jsonb - omit multiple nested keys

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

Upvotes: 1

Views: 324

Answers (2)

Ihor Sakailiuk
Ihor Sakailiuk

Reputation: 6068

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.

Upvotes: 2

klin
klin

Reputation: 121624

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.

Upvotes: 3

Related Questions