Search code examples
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:

    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'
))