Remove nested json object from array in postgres

Question

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

Will it always be under keys c and e?

Schwern
– Schwern

2020-12-08 02:41:59 +00:00
Commented Dec 8, 2020 at 2:41 — Schwern
– Schwern, Commented Dec 8, 2020 at 2:41
Yes it will always be under keys c and e @Schwern

Matt
– Matt

2020-12-08 02:43:13 +00:00
Commented Dec 8, 2020 at 2:43 — Matt
– Matt, Commented Dec 8, 2020 at 2:43

S-Man · Accepted Answer · 2020-12-08 08:19:06Z

0

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;

To remove a certain element from a JSON array, you need to expand it into on row per array element
Remove the record with the element you want to delete
Reaggregate the JSON array
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)

answered Dec 8, 2020 at 8:19

S-Man

24k9 gold badges51 silver badges78 bronze badges

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

Collectives™ on Stack Overflow

Remove nested json object from array in postgres

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related