Complex nesting with array

SQL++
1

I have 2 documents, both in form of array of objects. Now, I would want to nest one of the document’s object in the other document. Below are the documents:
doc1:
{
“docType”: “pickup”,
“data”: [
{
“hotelnum”: “PHLAL”,
“pickupcode”: “ALL”,
“description”: “All Charges”
}
]
}
doc2:
{
“docType”: “pickupdetail”,
“data”: [
{
“hotelnum”: “PHLAL”,
“pickupcode”: “ALL”,
“depnum”: 22,
“subdepnum”: 10,
“posthotelnum”: “PHLAL”
}
]
}
Now, i want a doc3 which would look like:

doc3:
{
“docType”: “pickup”,
“data”: [
{
“hotelnum”: “PHLAL”,
“pickupcode”: “ALL”,
“description”: “All Charges”
“pkd”:
[
{
“depnum”: 22,
“subdepnum”: 10,
“posthotelnum”: “PHLAL”
}
]
]
}}

Any idea how I could implement this using N1QL?

Many thanks in advance :slight_smile:

2 
SELECT d.*,  ARRAY OBJECT_ADD(dv1,"pkd", ARRAY {dv2.depnum, dv2.subdepnum,dv2. posthotelnum} FOR dv2 IN d2.data WHEN dv1.hotelnum = dv2.hotelnum AND dv1.pickupcode  = dv2.pickupcode END) FOR dv1 IN d.data END AS data
FROM default AS d1  USE KEYS ["doc1"]
LET d2 = (SELECT RAW d FROM default AS d USE KEYS ["doc2"])[0];
1 Like
3

Hey @vsr1

the query works after making tiny modifications to it. Below is the query which gives me the resultant doc3:

SELECT ARRAY OBJECT_ADD(dv1,"pickupDetail", ARRAY {dv2.depnum, dv2.subdepnum,dv2.seq,dv2.rteguestnum,dv2.folioid} FOR dv2 IN d2.data WHEN dv1.hotelnum = dv2.hotelnum AND dv1.pickupcode  = dv2.pickupcode END) 
FOR dv1 IN d1.data END AS pickupCds
FROM `default` AS d1  USE KEYS ["doc1"]
LET d2 = (SELECT RAW d FROM `default` AS d USE KEYS ["doc2"])[0];

Can you please tell me what [0] at the end does?

4

subquery in this context is expression and it returns results as ARRAY. In your case ARRAY has single or no object.
[0] means get 0th element OF ARRAY.

5

thanks @vsr1

as usual, you’re always of huge help