Remove a whole 3rd level json array using query

Couchbase Server
, ,
1

I am trying to remove a 3rd level array containing a specific id using update & set but it seems to be completely deleting the hero_upgrade array.

Json Document:

{
  "id": 0,
  "avatar": {
    "name": "blabla",
	"hero_upgrade": [
      {
        "id": 28000007,
        "cnt": 29
      },
      {
        "id": 28000002,
        "cnt": 49
      },
      {
        "id": 28000000,
        "cnt": 69
      },
      {
        "id": 28000008,
        "cnt": 8
      },
      {
        "id": 28000012,
        "cnt": 19
      }
    ]
  },
  "jsonType": "data"
}

Query:

UPDATE magic-players
SET avatar.hero_upgrade = ARRAY v FOR v IN hero_upgrade WHEN v.id != 28000007 END
WHERE jsonType = "data" AND
ANY v IN avatar.hero_upgrade SATISFIES v.id = 28000007 END;

I am trying to remove the whole array which contains the id 28000007 while keeping every other array so it looks like:

"hero_upgrade": [
  {
    "id": 28000002,
    "cnt": 49
  },
  {
    "id": 28000000,
    "cnt": 69
  },
  {
    "id": 28000008,
    "cnt": 8
  },
  {
    "id": 28000012,
    "cnt": 19
  }
 ]

What would be the correct way to get this done?

EDIT: removed “” from ids in query code (it still is giving the same result as mentioned)

2

You have right query only issue is you are using string vs number (remove quotes). In JSON type is attached to value.
(i.e. 1 is different from “1”, no implicit casting)

UPDATE `magic-players`
SET avatar.hero_upgrade = ARRAY v FOR v IN hero_upgrade WHEN v.id != 28000007 END
WHERE jsonType = "data" AND
ANY v IN avatar.hero_upgrade SATISFIES v.id = 28000007 END;
1 Like
3

oh forgot to upload the edited code, my bad!
but it seems to be deleting the whole hero_upgrade array instead of only deleting the array inside which id 28000007 exists

4

There is no array inside id 28000007. You need to post full document and expecting output.
Otherwise it is very difficult.

{
  "id": 0,
  "avatar": {
    "name": "blabla",
	"hero_upgrade": [
      {
        "id": 28000007,
        "list": [1,2,3,4]
        "cnt": 29
      },
      {
        "id": 28000002,
        "cnt": 49
      }
    ]
  },
  "jsonType": "data"
}

If you want remove list where id 28000007

UPDATE `magic-players`
UNSET  hu.list  FOR hu IN avatar.hero_upgrade  WHEN v.id == 28000007 END
WHERE jsonType = "data" AND
ANY v IN avatar.hero_upgrade SATISFIES v.id = 28000007 AND v.list IS NOT MISSING END;

Check Examples 3-5 https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/update.html