Update object elements along with child array when object.name exists or else append

SQL++
1

Hi,
I’m new to Couchbase and I have my document JSON schema as mentioned below;

{
"id": "C123",
"type":"Base",
"elementList":[
         {
              "name":"name1",
               "ind":true,
               "otherInd": true,
               "fieldList":[
                     {
                          "key":1,
                           "elemSrc":"val1",
                            "indctr" : "N",
                             "reason": "reason1"
                      },
                       {
                          "key":2,
                           "elemSrc":"val2",
                            "indctr" : "N",
                             "reason": "reason2"
                      }
               ]
         },
         {
              "name":"name2",
               "ind":true,
               "otherInd": true,
               "fieldList":[
                     {
                          "key":1,
                           "elemSrc":"val1",
                            "indctr" : "N",
                             "reason": "reason1"
                      },
                       {
                          "key":2,
                           "elemSrc":"val2",
                            "indctr" : "N",
                             "reason": "reason2"
                      }
               ]
         }
]
}

I need to update “elementList” on the following scenarios:

  1. The name field is distinct in first level array, so I need to update “ind” and “otherInd” fields for a given “name” in first level array and along with that it should update “reason” and “ind” fields in “fieldList”(second level array) for a given combination of “key” and “elemSrc” , the combination of “key” and “elemSrc” are distinct for a given “name” in the first level array.
  2. If name does not exist in first level array then it should create a new object and append to “elemList” array and similarly if the combination of “key” and “elemSrc” in “fieldList” is not present then the new object will be appended to the existing “fieldList” array.

I’m trying to use N1ql Update Query to achieve this, can anyone please help me on the following.

Thanks!

2 
UPDATE default AS d
SET d.elementList = CASE WHEN "name1" NOT IN d.elementList[*].name 
                       THEN ARRAY_APPEND(d.elementList, {"name":"name1","ind":false,"otherInd":false,"fieldList":[{"key":2,"elemSrc":"val1","reason":"reason1", "indctr":"Y"}]}) 
                       ELSE d.elementList
                      END,
  d.elementList[pos] = 
         OBJECT_CONCAT(el, {"ind": true, "otherInd": true, 
                            "fieldList": CASE WHEN (ANY v IN el.fieldList SATISFIES v.`key` = 2 AND v.elemSrc = "val1" END)
                                              THEN (ARRAY (CASE WHEN fl.`key` = 2 AND fl.elemSrc = "val1" 
                                                               THEN OBJECT_CONCAT(fl, {"reason": "rason1", "indctr":"Y"}) 
                                                               ELSE fl 
                                                               END)
                                                   FOR fl IN el.FieldList 
                                                   END)
                                               ELSE ARRAY_APPEND(el.FieldList ,{"key":2,"elemSrc":"val1","reason":"reason1", "indctr":"Y"})
                                               END })
         FOR pos:el IN d.elementList WHEN el.name = "name1" END
WHERE ...