How to update/add object in nested Array on Couchbase document?

[souravarya5858]

Hi,

I have below Json data in the document name “abcd_2_15667:0” on the Bucket: admin, Scope : _default and Collection : server.

{
“service”: {
“id”: “15667:0”,
“serviceActionRequest”:[
{
“id”: “f1256812-ad68-4d60-3e5ec-1f9e-edb”,
“serviceActionRequestStatus”: [
{
“id”: “bd3074d0-45db-85ae-f46ae84d2880”,
“operation”: “add”,
“status”: “failure”,
“message”: “Request failed with status code 500”
}
]
}
],
“serviceState”: “Suspend”
}
}

I wanted to add a new Object {“id”: “bd30-74d0-35bc-85ae-f46ae84d-2880”,“operation”: “add”,“status”: “failure”,“message”: “Policy Failed:Request failed with status code 500”} on the “serviceActionRequestStatus” array which is nested under “serviceActionRequest” array.

What query I have to write to achieve this requirement, please help me here?

I am new in couchbase and I tried to write some query which helps me to add object on “serviceActionRequest” object but not in “serviceActionRequestStatus”.

Below is the query:-
UPDATE admin._default.server AS service
SET service.serviceActionRequest = ARRAY_DISTINCT(ARRAY_APPEND(service.serviceActionRequest, {“language”: “en”,“locale”: “CA”,“primary”: “false”})) WHERE service.id=‘15667:0’;

Required Answer like this :
{
“service”: {
“id”: “15667:0”,
“serviceActionRequest”:[
{
“id”: “f1256812-ad68-4d60-3e5ec-1f9e-edb”,
“serviceActionRequestStatus”: [
{
“id”: “bd3074d0-45db-85ae-f46ae84d2880”,
“operation”: “add”,
“status”: “failure”,
“message”: “Request failed with status code 500”
},
{
“id”: “bd30-74d0-35bc-85ae-f46ae84d-2880”,
“operation”: “add”,
“status”: “failure”,
“message”: “Policy Failed:Request failed with status code 500”
}

        ]
      }
    ],
    "serviceState": "Suspend"
  }
}

Please help me to give your valuable answers.
Thank You.

[dh]

You could try something like:

UPDATE `admin`.`_default`.`service`
USE KEYS ["abcd_2_15667:0"]
SET `serviceActionRequest` = ARRAY 
                               CASE
                               WHEN v.id = "f1256812-ad68-4d60-3e5ec-1f9e-edb"
                               THEN
                                 OBJECT_PUT(v
                                           ,"serviceActionRequestStatus"
                                           ,ARRAY_DISTINCT(ARRAY_APPEND(v.`serviceActionRequestStatus`
                                                                       ,{
                                                                         "id":"bd30-74d0-35bc-85ae-f46ae84d-2880"
                                                                        ,"operation":"add"
                                                                        ,"status":"failure"
                                                                        ,"message":"Policy Failed:Request failed with status code 500"
                                                                        }
                                                                       )
                                                          )
                                           )
                               ELSE
                                 v
                               END
                             FOR v IN `serviceActionRequest` 
                             END
;

to add the specific element to the specific serviceActionRequest.serviceActionRequestStatus array.

I do note the differing serviceActionRequestStatus.id field formats; they won’t be seen as equivalent of course. Further, if all you’re looking to do is update some fields in the array, this won’t do it - ARRAY_DISTINCT would see any differing field values (e.g. message) as distinct. If you wanted to say update the message field for the existing element, you could use something like:

UPDATE `admin`.`_default`.`service`
USE KEYS ["abcd_2_15667:0"]
SET `serviceActionRequest` = ARRAY 
                               CASE
                               WHEN v.id = "f1256812-ad68-4d60-3e5ec-1f9e-edb"
                               THEN
                                 OBJECT_PUT(v
                                           ,"serviceActionRequestStatus"
                                           ,ARRAY 
                                              CASE
                                              WHEN s.id = "bd3074d0-45db-85ae-f46ae84d2880"
                                              THEN
                                                OBJECT_PUT(s,"message","Policy Failed:"||s.message)
                                              ELSE
                                                s
                                              END
                                            FOR s IN v.`serviceActionRequestStatus`
                                            END
                                           )
                               ELSE
                                 v
                               END
                             FOR v IN `serviceActionRequest` 
                             END
;

HTH.

Ref:

https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/collectionops.html#array
https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/conditionalops.html

[souravarya5858]

Thank you so much.
This is working as per requirement.