DELETE index from nested array N1ql

Couchbase Server
,
1

Hi ,

I want to delete a index from nested array

{
  "id": "3221220001",
  "schedule_name": "key1",
  "status": "Active",
  "created_date": "2022-03-08",
  "created_branch_id": "3221",
  "updated_date": "2022-03-08 04:49:02",
  "schedule_branches": [],
  "schedule_conditions": [],
  "type": "schedule",
  "schedule_pricelist": [
    {
      "serviceId": "3221190019",
      "serviceName": "Pants",
      "serviceType": "Alteration",
      "servicePicture": "da156ec80ddf42696347761ba3d45924.png",
      "servicePriority": 1,
      "departmentId": "3221190019",
      "departmentName": "Hem",
      "deptPicture": "94c0a8b37c943f9921e8556aaddd7097.png",
      "deptPriority": 1,
      "itemId": "3221220000",
      "itemName": "Single",
      "rack": "rack",
      "taxes": "not_exempt",
      "itemType": "Product",
      "itemPrice": 12,
      "popupList": []
    }
    
  ]
}

Here what i am trying to do so far

UPDATE bucket b USE KEYS 'schedule_3221220001'
SET b.schedule_pricelist = ARRAY v FOR v IN b.schedule_pricelist WHEN v.serviceId != "3221190019" AND v.departmentId != '3221190019' END

the problem is it is deleting everything from schedule_pricelist . i just want to delete whose serviceId and departmentId get matched.

2

If both values are same then use AND otherwise use OR.
If all elements match then it can end up empty array.

WITH vals AS ( {"type": "schedule",
            "schedule_pricelist": [{"serviceId": "3221190019","departmentId": "3221190019"},
                                   {"serviceId": "3221190020","departmentId": "3221190018"}
                                  ] })
SELECT ARRAY v FOR v IN vals.schedule_pricelist WHEN v.serviceId != "3221190019" OR v.departmentId != "3221190019" END AS schedule_pricelist;
3

When i run the above query it is fetching the record i want to delete the record from nested array here is the result i am getting

[
{
“schedule_pricelist”: [
{
“departmentId”: “3221190018”,
“serviceId”: “3221190020”
}
]
}
]

4

I gave example how ARRAY … works via Select. Same will work when used in UPDATE.

UPDATE bucket b USE KEYS 'schedule_3221220001'
SET b.schedule_pricelist = ARRAY v FOR v IN b.schedule_pricelist WHEN v.serviceId != "3221190019" OR v.departmentId != '3221190019' END

If all elements deleted it will be empty.