postgresqljsonb

Delete json object from jsonb type column based on key of json object in postgresql

can anybody tell me query to remove entire json object from array in jsonb type column by given id stored in json object.

example : I have data column in t1 table. Type of data column is jsonb. Record in data column is as follows

"type" : "users",
"info" : [
{
"id":1,
"name" : "user1"
},
{
"id":2,
"name" : "user2"
}
]
}

Now I want to delete entire json object which has id = 1 ( I want to identify the object by json object id)

The expected result is

{
"type" : "users",
"info" : [
{
"id":2,
"name" : "user2"
}
]
}

Please help me out with queries. Thanks in advance šŸ™‚

Solution

  • You will need to use a subquery for each row on jsonb_array_elements() that are then aggregated back to an array:
    demo at db<>fiddle

    UPDATE t1
SET data = jsonb_set(data, '{info}', (
      SELECT COALESCE(jsonb_agg(element), '[]'::jsonb)
      FROM jsonb_array_elements(data -> 'info') element
      WHERE element ->> 'id' <> '1'
    ))
WHERE data @> '{"info":[{"id":1}]}'
RETURNING jsonb_pretty(data);
    {
    ā€‡ā€‡ā€‡ā€‡"info": [
    ā€‡ā€‡ā€‡ā€‡ā€‡ā€‡ā€‡ā€‡{
    ā€‡ā€‡ā€‡ā€‡ā€‡ā€‡ā€‡ā€‡ā€‡ā€‡ā€‡ā€‡"id": 2,
    ā€‡ā€‡ā€‡ā€‡ā€‡ā€‡ā€‡ā€‡ā€‡ā€‡ā€‡ā€‡"name": "user2"
    ā€‡ā€‡ā€‡ā€‡ā€‡ā€‡ā€‡ā€‡}
    ā€‡ā€‡ā€‡ā€‡],
    ā€‡ā€‡ā€‡ā€‡"type": "users"
    }

    The where condition uses containment to makes sure that this doesn't rewrite rows, unless:

    1. data root is a JSON type object, not a scalar, not an array, not null
    2. in the root object there's an "info" key present
    3. the "info" key holds an array, enabling jsonb_array_elements(), which would otherwise fail and raise an exception if there were scalars or objects under that path
    4. inside that array there's an object with an "id" key,
    5. its value is 1 that we're after

    @> uses an index if data has one - both jsonb_ops as well as jsonb_path_ops work