CODEWITHSUNDEEP
postgresqljsonb
Sam Esmail
Sam Esmail

Reputation: 383

Remove multiple keys from jsonb column in one statement

I'm trying to remove multiple keys from a jsonb column.

I've managed to get it to delete a single key with the following statement:

UPDATE table_a
SET data_column = data_column #- '{attr_1}'
WHERE type = 'type_a'

I understand thats a path so I can't do: #- '{attr_1,attr_2}

Based on the docs I should be able to also do - 'attr_1' but that didn't work otherwise I'd attempt to try - 'attr_1,attr2'

Upvotes: 22

Views: 14723

Answers (6)

jian
jian

Reputation: 4824

UPDATE table_a
SET data_column = data_column - 'attr_1'
WHERE type = 'type_a';

Upvotes: 1

Tim Kretschmer
Tim Kretschmer

Reputation: 2280

we wanted to remove the template_approval_type from the setting of our table templates. setting was a JSONb column.

query was as easy as:

UPDATE templates SET setting = setting::jsonb #- '{template_approval_type}'

Upvotes: 0

mylnz
mylnz

Reputation: 424

Multiple usages of the minus operator, like;

UPDATE table_a
SET data_column = data_column - 'attr_1'- 'attr_2'- 'attr_3'
WHERE type = 'type_a'

Upvotes: 5

Ihor Sakailiuk
Ihor Sakailiuk

Reputation: 6068

If you need to remove multiple non-nested keys you can use - operator:

SELECT '{ "a": 1, "b": 2, "c": 3 }'::jsonb - ARRAY['a', 'b'];

Upvotes: 17

Gerard H. Pille
Gerard H. Pille

Reputation: 2578

Or the minus operator once (but using a text array):

SELECT '{ "a": 1, "b": 2, "c": 3 }'::jsonb - '{a,b}'::text[];
 ?column? 
----------
 {"c": 3}
(1 row)

Upvotes: 23

Laurenz Albe
Laurenz Albe

Reputation: 246698

That should be as simple as applying the #- operator multiple times:

SELECT '{ "a": 1, "b": 2, "c": 3 }'::jsonb #- '{a}' #- '{b}';

 ?column? 
----------
 {"c": 3}
(1 row)

Upvotes: 22

Related Questions