Couchbase N1QL - Nest array using keys

Question

i'm new to Couchbase and N1QL syntax and i'm facing an issue. Let's say we have 3 type of documents:

Doc1 of TypeA with key = typeA:Doc1

{
  "type": "typeA"
  "id": "Doc1",
  "sequences": [
    "typeB:Doc2"
  ]
}

Doc2 of TypeB with key = typeB:Doc2

{
  "id": "Doc2",
  "processors": [
    {
      "order": 1,
      "id": "typeC:Doc3"
    }
  ]
}

Doc3 of TypeC with key = typeC:Doc3

{
  "id": "Doc3",
  "prop": "value"
}

What i want to achieve is to nest these 3 objects by their document keys in ordere to have a unique document with this structure:

{
  "id": "Doc1",
  "sequences": [
    {
      "id": "Doc2",
      "processors": [
        {
         "order": 1,
         "id": "Doc3",
         "prop": "value"
        }
       ]
    }
  ]

What i've done is to nest the first two documents to obtain a partial result. But i'm tryng to integrate also the third document.

Here's my attempt:

SELECT dev.*,
       ARRAY sq_i FOR sq_i IN prseq END AS sequences
FROM data dev 
NEST data prseq ON KEYS dev.sequences
WHERE dev.type = 'TypeA'

Can anyone help me with the third level of nesting? Thank you.

Answer 1

Use subqueries

SELECT dt.*,
    (SELECT ds.*,
       (ARRAY OBJECT_ADD((SELECT RAW dp FROM data AS dp USE KEYS v.id)[0], "order", v.`order`)
       FOR v IN ds.processors
       END) AS processors
     FROM data AS ds USE KEYS dt.sequences) AS sequences
FROM data AS dt
WHERE dt.type = 'TypeA';