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

Answer 1

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)