Updating/inserting elements in an sub-document array

I’m having trouble understanding how to craft a N1QL query to perform an upsert with the elements(json objects) of an array in the given document below (bucket X, meta(x).id = 1256.

{
  "Note:": "dummy test data",
  "userId": 1256,
  "incentivePrograms": [
    {
      "incentiveProgramId": "a22087dc-81a0-4604-8559-733ffa0f7845",
      "userMembershipId": "23dvc",
      "membershipStatus": "active"
    }
  ],
  "incentiveProgramPreferences": [
    {
      "universityId": "3056",
      "IncentiveProgramID": "a22087dc-81a0-4604-8559-733ffa0f7845"
    }
  ]
}

The array I want to merge an array element into for this document is the “incentivePrograms”. The merge should, on query parameter “ip_id” matching “IncentiveProgramId” value, the object is replaced with a new object, on no matching elements the new object would be appended to the array.

I can’t even get an append to work (I get no errors and empty results in the workbench):

SELECT ARRAY_APPEND(im.incentivePrograms,{
      "incentiveProgramId": "1422e858-bae7-48ab-befe-88e9a95ced2d",
      "userMembershipId": "dud",
      "membershipStatus": "inactive"
    })
FROM default:`X` im    
WHERE META(im) = 1256

I also tried this:

UPDATE default:`X` im
SET im.incentivePrograms = ARRAY_APPEND(im.incentivePrograms, {
               "incentiveProgramId": "1422e858-bae7-48ab-befe-88e9a95ced2d", "userMembershipId": "dude", "membershipStatus": "inactive" }
               )
WHERE META(im) = 1256

Which is patterned after on of the couchbase doc pages.
Do I need to use an UNNEST somehow, or some special subquery, or CASE structure somehow?

I understand that upserts can only be done at the document level, and not within the document.

WHERE META(im) = 1256

META(im).id is document key not META() , and also it must be string not a number.

UPDATE X AS im
SET im.incentivePrograms = CASE WHEN (ANY v IN im.incentivePrograms SATISFIES v.incentiveProgramId = $ip_id END)
                                THEN
                                      ARRAY  (CASE WHEN v.incentiveProgramId = $ip_id THEN $newobj ELSE v END)
                                      FOR v IN im.incentivePrograms
                                      END
                                ELSE
                                    ARRAY_APPEND(im.incentivePrograms, $newobj)
                                END
WHERE im.userId = 1256 ;

a slight improvement is to remove the where clause and add a USE KEYS hint to the update.

Hi @vsr1 is this solution similar to mutateIn operations? Is it sub document operation or more similar to whole document operation? I try to find the optimum solution for fragment updates of large documents. Already asked the question regarding the exact issue here: Array Indexation consistency in sub document mutations

Also I have question how to remove element from the array in similar way to this update?

With N1QL you have to use UPDATE statement either constructing new array or use ARRAY functions and it is whole document update.
If you plan to do large set of documents explore eventing (EE) option. Examples: Using the Eventing Service | Couchbase Docs