postgresql UPDATE to remove key/value from JSONB data type

Question

Given the following table and data:

CREATE TABLE test (  
  slots jsonb
);

INSERT INTO test VALUES ('{"0": {"tag": "abc", "info": "xyz"}, "1": {"tag": "def", "info": "uvw"}}');
SELECT slots FROM test;

Now I want to delete the "1" key/value entirely. I can set it to null as follows:

UPDATE test SET slots['1'] = null;
SELECT slots FROM test;

But this returns:

{"0": {"tag": "abc", "info": "xyz"}, "1": null}

Whereas I want it to return:

{"0": {"tag": "abc", "info": "xyz"}}

What is the command/syntax to achieve this?

user330315 · Accepted Answer · 2021-11-03 10:32:01Z

4

Use the - operator to remove a key completely

update test 
  set slots = slots - '1'

answered Nov 3, 2021 at 10:32

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

drmrbrewer Over a year ago

P.S. does this apply to postgresql 14 too? I know it will work, but postgresql 14 comes with new operators for accessing jsonb data, so just checking there isn't a new version of the - operator.

user330315 Over a year ago

I don't think the new subscripting supports removal of keys

Collectives™ on Stack Overflow

postgresql UPDATE to remove key/value from JSONB data type

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related