0

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

askedDec 8, 2020 at 2:34
Matt's user avatar
2
  • 1
    Will it always be under keys c and e?CommentedDec 8, 2020 at 2:41
  • Yes it will always be under keys c and e @SchwernCommentedDec 8, 2020 at 2:43

1 Answer1

0

step-by-step demo:db<>fiddle

UPDATE t SET data = jsonb_set(data::jsonb, '{c,e}', s.new_array::jsonb)::json   -- 4FROM (    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 anUPDATE on your table, you could use thejsonb_set() function to update the JSON element with your newly created array. Unless you are not using typejsonb, you have to case your JSON data intojsonb (and the result back to typejson)
answeredDec 8, 2020 at 8:19
S-Man's user avatar
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

Sign up orlog in

Sign up using Google
Sign up using Email and Password

Post as a guest

Required, but never shown

By clicking “Post Your Answer”, you agree to ourterms of service and acknowledge you have read ourprivacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.