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.

closed as unclear what you're asking by Evan Carroll, RLF, Andriy M, Marcello Miorelli, Erik Feb 24 '17 at 16:04

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 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.