Postgresql - update or delete a value from a nested jsonb element

Question

Is there a way to delete/update a nested json key object (not array) for example the following json:

{
  "top": {
    "nested": {
       "leaf": 1
    }
  }  
}

how would I delete/update the leaf element?

I tried

SELECT jsonb '{"top": {"nested": {"leaf" : 1}}' - '{top,nested,leaf}'

but no luck

Marth · Accepted Answer · 2017-07-05 17:02:51Z

40

You need to use the #- operator, not -:

SELECT jsonb '{"top": {"nested": {"leaf" : 1}}}' #- '{top,nested,leaf}';
┌─────────────────────────┐
│        ?column?         │
├─────────────────────────┤
│ {"top": {"nested": {}}} │
└─────────────────────────┘
(1 row)

From the documentation:

- (given a text argument): Delete key/value pair or string element from left operand. Key/value pairs are matched based on their key value.
- (given an int argument): Delete the array element with specified index (Negative integers count from the end). Throws an error if top level container is not an array.
#-: Delete the field or element with specified path (for JSON arrays, negative integers count from the end)

answered Jul 5, 2017 at 17:02

Marth

24.9k3 gold badges68 silver badges76 bronze badges

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

2 Comments

PainIsAMaster Over a year ago

How does it know what table to perform that one THOUGH

Marth Over a year ago

@ennth: what do you mean? If you had a table t with a jsonb column j you would do SELECT j -# '{to,remove}' FROM t.

Collectives™ on Stack Overflow

Postgresql - update or delete a value from a nested jsonb element

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