Update nested array document using N1Ql

SQL++
1

Hi Team,

I have dynamic document like below structure, I want to update productComponents array which ever missing key ‘y’.
I want to update document with “y”:2 on which ever object its missing on productcomponents how to achieve it using n1ql.

{
“id”: “appliedProduct_2_11163”,
“type”: “appliedProduct”,
“productBundles”: [
{
“x”: 1,
“productComponents”: [
{
“x”: 1,
“y”: 2
},
{
“x”: 1
},
{
“x”: 1,
“y”: 2
}
],
“productBundles”: [
{
“offerId”: 10005672,
“productInstId”: 256,
“activeDt”: “2019-03-26T16:14:54Z”,
“productComponents”: [
{
“x”: 1
}
]
},
{
“offerId”: 10008222,
“productInstId”: 257,
“activeDt”: “2019-03-26T16:14:54Z”,
“productComponents”: [
{
“x”: 1
}
]
},
{
“offerId”: 10006303,
“activeDt”: “2019-03-26T16:14:54Z”,
“productInstId”: 258
},
{
“offerId”: 10009300,
“activeDt”: “2019-03-26T16:14:54Z”,
“productInstId”: 259
},
{
“offerId”: 10008252,
“productInstId”: 255,
“activeDt”: “2019-03-26T16:14:54Z”,
“productComponents”: [
{
“x”: 1,
“y”: 2
}
]
},
{
“offerId”: 10005956,
“activeDt”: “2019-03-26T16:14:54Z”,
“productInstId”: 260
}
]
}
]
}

2 
UPDATE default AS d
SET pc.y = 2 
          FOR pc IN pb.productComponents 
                      FOR pb IN d.productBundles WHEN pc.y IS MISSING END
WHERE d.type = "appliedProduct"  AND ANY pb IN  d.productBundles SATISFIES (ANY pc IN pb.productComponents  SATISFIES pc.y IS MISSING   END) END;

https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/update.html (Last example)

3

I am getting syntax error inabove query
{
“code”: 3000,
“msg”: “syntax error - at SATISFIES”,

Also I am trying to update pc.y with below json details
“inv”:
{ “assignments”: “3”,
“Type”: “1”,
“views”: “2049”
}

4 
UPDATE default AS d
SET pc.y = {"inv": { "assignments": "3", "Type": "1", "views": "2049" }}
          FOR pc IN pb.productComponents 
                      FOR pb IN d.productBundles WHEN pc.y IS MISSING END
WHERE d.type = "appliedProduct"  AND ANY pb IN  d.productBundles SATISFIES (ANY pc IN pb.productComponents  SATISFIES pc.y  IS MISSING END) END;
5

It is not updating the correct data.
It is updating below structure:
{
“id”: “appliedProduct_2_11163”,
“type”: “appliedProduct”,
“productBundles”: [
{
“productComponents”: [
{
“x”: 1,
“y”: 2 // it is updating here i.e only level two
}
]
}
]
}

But I want to update in below structure:
{
“accountNo”: 11163,
“id”: “appliedProduct_2_11163”,
“productBundles”: [
{
“x”: 1,
“productBundles”: [
{
“x”: 1,
“productComponents”: [
{
// I need to add new information here
}
]
}
]
}
]
}

6 
UPDATE default AS d
SET pc.y = {"inv": { "assignments": "3", "Type": "1", "views": "2049" }}
          FOR pc IN pb.productComponents 
                      FOR pb IN pb1.productBundles  FOR pb IN d.productBundles WHEN pc.y IS MISSING END
WHERE d.type = "appliedProduct"  AND ANY pb IN  d.productBundles SATISFIES (ANY pb1 IN pb.productBundles 
 SATISFIES (ANY pc IN pb1.productComponents  SATISFIES pc.y  IS MISSING END) END) END;

If you want to update productComponents any where in the document try following

UPDATE default AS d
SET pc.y = {"inv": { "assignments": "3", "Type": "1", "views": "2049" }}
          FOR pc IN p.productComponents FOR p WITHIN d WHEN pc.y IS MISSING END
WHERE d.type = "appliedProduct"
      AND ANY p WITHIN d SATISFIES (ANY pc IN p.productComponents SATISFIES pc.y IS MISSING END) END;
7

Thanks vsr1 it works.

8

@vsr1
Would it be possible to get the FOR stuff to work for SELECT as well? Much simpler to see if an update will work appropriately if we could use the same conditions for all query types.

So, for the example you provided:

SELECT pc FROM default as d
          FOR pc IN pb.productComponents 
                      FOR pb IN pb1.productBundles  FOR pb IN d.productBundles WHEN pc.y IS MISSING END
WHERE d.type = "appliedProduct"  AND ANY pb IN  d.productBundles SATISFIES (ANY pb1 IN pb.productBundles 
 SATISFIES (ANY pc IN pb1.productComponents  SATISFIES pc.y  IS MISSING END) END) END;

would return pc which would be the item being updated in the sample…

9

UPDATE SET clause syntax will not allowed in other places.
If you need you must use UNNEST or One of the following constructs build new one

ARRAY, FIRST, and OBJECT

Range transforms (ARRAY, FIRST, OBJECT) allow you to map and filter the elements or attributes of a collection or object(s). ARRAY evaluates to an array of the operand expression, while FIRST evaluates to a single element based on the operand expression. OBJECT evaluates to an object whose name : value attributes are name-expr : expr .

Name-expr must evaluate to a string. If not, that attribute is omitted from the result object.

range-xform:

ARRAY (ARRAY FOR pc IN pb WHEN pc.y IS MISSING END) FOR pb IN d.productBundles END;

It generates ARRAY of ARRAY that contains y is MISSING