2

I am attempting to remove an element from my database. The element I want to remove is within a JSON object called playerContainer which contains an array named players. This all sits within a table called site_content. I am trying to remove the object based on it's ID.

Here is the sample JSON

"playersContainer": {
        "players": [
            {
                "id": "1",
                "name": "Nick",
                "teamName": "Shire Soldiers",
                "ratings": [
                    1,
                    5,
                    6,
                    9
                ],
                "assists": 17,
                "manOfTheMatches": 20,
                "cleanSheets": 1,
                "data": [
                    3,
                    2,
                    3,
                    5,
                    6
                ],
                "totalGoals": 19

            },
}

Below is the query I am attempting to use to delete the object within the array

UPDATE site_content
SET content = jsonb_set(content, '{playersContainer,players}'::text[], content->'playersContainer'->'id'->'players' #- '{1}'::jsonb

However this seems to delete all the data from my content row in my table and not just the array with an ID of 1 in my players array.

  • 1
    Paste the JSON you want afterward. This is also not valid json. – Evan Carroll Feb 20 '17 at 20:42
3

To remove key (warning - json/jsonb arrays index starts from 0 unlike pure SQL arrays):

UPDATE site_content
   SET content #- '{playersContainer,players,0}'::text[];

To remove array element with specific id:

UPDATE site_content
   SET content = content #- coalesce(('{playersContainer,players,' || (
            SELECT i
              FROM generate_series(0, jsonb_array_length(content->'playersContainer'->'players') - 1) AS i
             WHERE (content->'playersContainer'->'players'->i->'id' = '"2"')
         ) || '}')::text[], '{}');
  • This works fine when you only get one resulting index back, but what would you do if you get multiple rows as a result? – Maximilian Lindsey Aug 15 '17 at 12:43
  • I found out what to do if you got more then one result: just add a from statement after the set, and put the whole index generation part in that from. I also need to append a whereclause after the from stating that it should be updated where the from-result id matched the original table id. – Maximilian Lindsey Aug 15 '17 at 13:05
  • in this case when players id not found value is null. i need a json object as it is. – Pranay Soni Sep 8 '18 at 16:36
  • Fixed - please try – Roman Tkachuk Sep 8 '18 at 18:38

Not the answer you're looking for? Browse other questions tagged or ask your own question.