Join with same name nesting

SQL++
1

Hello Team,

The below is the document which has the two same nesting name as Bundle. I would like to select the column which is Bundle.Bundle.component. compinst .


[
  {
    "document": {
      "acc": 1234,
      "id": "ap_2_1234",
      "Bundle": [
        {
          "activeDt": "2020-10-29T16:00:00Z",
          "connectReason": 1,
          ---
		  ----
          "Bundle": [
            {
              "activeDt": "2021-03-14T21:25:50Z",
              "offerId": 566697,
              "component": [
                {
                  "activeDt": "2020-10-30T16:00:00Z",
                  -------
                  },
                  "compid": abcdef,
                  "compinst": 999999,
                  "compinst2": 3,
                  -----
				  ----
				  ---
				  
                }

I have tried the below query

select comp.compinst from document d unnest d.bundle as b1 unnest b1.bundle as b2 unnest b2.component as comp
where d.acc=1234

But no result returned. It is giving logical error while trying to unnest in b2. Please help to solve the query.

Regards
Dhaval.

2

JSON fields are case-sensitive bundle vs Bundle

SELECT comp.compinst 
FROM  document d 
UNNESTd.Bundle AS b1 
UNNEST b1.Bundle AS b2 
UNNEST b2.component AS comp
WHERE  d.acc=1234
3

Many thanks. It has worked. Now I would like to make it an update query but it is failing.

update 'document` as d unnest d.Bundles AS b1
unnest b1.Bundles As b2
unnest b2.components as comp
set comp.inactiveDt=CLOCK_LOCAL()
where d.accountNo=1234and d.type=“appliedProduct” and comp.compinst =999999;

But it gives the error.

{
“code”: 3000,
“msg”: “syntax error - at '”,

Regards
Dhaval.

4 
UPDATE `document` AS d
SET comp.inactiveDt=CLOCK_LOCAL()
       FOR comp IN b2.components 
          FOR b2 IN b1.Bundles  
             FOR b1 IN d.Bundles WHEN  comp.compinst =999999 END
WHERE  d.type="appliedProduct"  AND d.accountNo=1234 ;

https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/update.html#update-for

5

Hi,

Thank you for providing the query. I have tried to execute the update statement but it is failing on the below error.

[
{
“code”: 3000,
“msg”: “syntax error - at '”,

What could be the reason?

6

There is no syntax error in above query. Post exact query you have done

7

Hi, You are right, after changing the date format, I am able to update the record.

UPDATE document AS d
SET comp.inactiveDt=CLOCK_TZ(‘UTC’)
FOR comp IN b2.components
FOR b2 IN b1.Bundles
FOR b1 IN d.Bundles WHEN comp.compinst =999999 END
WHERE d.type=“appliedProduct” AND d.accountNo=1234 ;

Many thanks as the issue is closed now.