Add nested attribute

SQL++
1

Hello,

I want a one nesting attribute in the existing json,
the bold and italic part we are going to add.

is it possible to it with the query.

componentInstId is unique in productComponents attribute
{
“id”: “appliedProduct_2_xxxxx”,
“type”: “appliedProduct”,
“productComponents”: [
{
“componentId”: 53xxxx43,
“subscrNoResets”: null,
“packageId”: 12xxxx1183,
“componentStatus”: 1,
“componentInstId”: 50811680,
“packageInstIdServ”: 3,
“componentInstIdServ”: 3,
“connectReason”: 1,
“offerId”: 20xxxxx324,
"inventory": {
*** “invTypeId”: 301,***
*** “viewId”: 13452547***
*** },***
“productInstId”: 586545,
“characteristicValues”: {
“dummyAgInter”: “0_AgInter”
},
“subscrNo”: null,
“activeDt”: “2020-05-08T16:00:00Z”,
“packageInstId”: 16xxxxx49
},
]
}

2

You could try something like:

update bucket                                                                                                                      
set `productComponents` = 
    (select raw array_replace(b1.`productComponents`,
                              elem,
                              object_add(elem,"inventory",{
                                                            "invTypeId":301,
                                                            "viewId":13452547
                                                          }
                                        )
                             )
     from bucket b1
          unnest b1.`productComponents` as elem
     where b1.id = "appliedProduct_2_xxxxx"
     and elem.`componentInstId` = 50811680
    )
where bucket.id = "appliedProduct_2_xxxxx"
;

Of course you can run the sub-select independently to verify the change is what is expected prior to running the update itself.

HTH.

3

Actually, that won’t work for you as you have elements that are explicitly NULL and so can’t be matched for equality in the ARRAY_REPLACE. This should take care of that:

update bucket set `productComponents` =  
(
  select raw array_append(oth,object_add(o,"inventory",{
                                                          "invTypeId":301,
                                                          "viewId":13452547
                                                       }
                                        )
                         )
  from bucket b1
  let o = (select raw ov 
           from bucket b2  
           unnest `productComponents` as ov 
           where b2.id = "appliedProduct_2_xxxxx" 
           and ov.`componentInstId` = 50811680
          )[0]
  , oth = (select raw otv 
           from bucket b3  
           unnest `productComponents` as otv 
           where b3.id = "appliedProduct_2_xxxxx"                                                                                   
           and otv.`componentInstId` != 50811680
          )
  where b1.id = "appliedProduct_2_xxxxx"
)
where bucket.id = "appliedProduct_2_xxxxx"
;

HTH.

4

A further simpler option:

UPDATE bucket b
SET pc.inventory = {"invTypeId":301, "viewId":13452547}
     FOR pc IN b.`productComponents` WHEN pc.`componentInstId` = 50811680 END
WHERE b.id = "appliedProduct_2_xxxxx";

Ref: Example 3 at UPDATE | Couchbase Docs
HTH.

1 Like