CODEWITHSUNDEEP
sqljsonpostgresql
Matt
Matt

Reputation: 99

Remove nested json object from array in postgres

I have a json object field in postgres that has the following shape

{
"a": {
   
},
"b": [
    {
        
    }
],
"c": {
    "d": "",
    "e": [
        {
            "id": 1234,
            "f": "",
            "g": ""
        }
    ]
}

}

I'd like to know how to write a statement that removes object's from e array where the id is 1234 in postgres. e could have multiple objects, if there are more than one objects in the e array, I want to keep those and only remove the object with the id of 1234.

Thanks

Upvotes: 1

Views: 1161

Answers (1)

S-Man
S-Man

Reputation: 23686

step-by-step demo:db<>fiddle

UPDATE t 
SET data = jsonb_set(data::jsonb, '{c,e}', s.new_array::jsonb)::json   -- 4
FROM (
    SELECT
        json_agg(value) as new_array                   -- 3
    FROM
        t,
        json_array_elements(data -> 'c' -> 'e')        -- 1
    WHERE value ->> 'id' != '1234'                     -- 2
) s;
  1. To remove a certain element from a JSON array, you need to expand it into on row per array element
  2. Remove the record with the element you want to delete
  3. Reaggregate the JSON array
  4. If you want to do an UPDATE on your table, you could use the jsonb_set() function to update the JSON element with your newly created array. Unless you are not using type jsonb, you have to case your JSON data into jsonb (and the result back to type json)

Upvotes: 1

Related Questions