Complex N1QL query in Coucbbase

Question

I have the following two types of documents in couchbase in same bucket called 'entities':

{
  "documentType": "person",
  "id": "4f3567cd-782d-4456-aabd-ff5faf071476",
  "personal": {
    "gender": "Male",
    "dob": "1984-05-22",
    "firstName": "Peter",
    "lastName": "Smith",
    "idNumber": "4915040000111"
  }
}

and:

{
    "id": "09690237-9ef5-4381-93dc-7312f7417f82",
    "documentType": "link",
    "entities": [
        {
            "id": "000151ef-f4b9-4cd4-bf3b-d5b07d79ed20",
            "type": "parent",
        },
        {
            "id": "000151ef-f4b9-4cd4-bf3b-d5b07d79ed21",
            "type": "child"
        }

    ]
}

The person document describes a person and the link table defines links between people. A parent can have multiple children and a parent will have a link document for every child he has.

Question: Given a parent id, return all the personal information for each of the children of that parent, eg.

[
    {
        "id": "000151ef-f4b9-4cd4-bf3b-d5b07d79ed21",
        "personal": {
            "gender": "Male",
            "dob": "2004-05-22",
            "firstName": "Chris",
            "lastName": "Smith",
            "idNumber": "0415050000111"
        }
    },
    {
        ...
    }
]

So far: I was able to get a list of all the children IDs using:

select array_flatten(array_agg(array entity.id for entity in entities.entities when entity.type = 'parent' end), 3) as entity
from entities
where entities.docuementType = 'link'
      and any entity within entities satisfies entity.id = '01f6a9eb-0d7e-4495-a90f-f96a38aef621'
                                               and entity.type='parent' end

However, it seems like couchbase cannot perform joins on sub-queries. Is there a way this can be accomplished using N1QL? At the moment my only solution is to get the the list of IDs and then run another query to obtain the children.

vsr vsr · Accepted Answer · 2018-01-11T13:35:51

SELECT p
FROM entities AS l 
JOIN entities AS p 
ON KEYS ARRAY e.id FOR e IN l.entities WHEN e.type = "child" END
WHERE l.docuementType = 'link'
      AND ANY entity IN l.entities 
              SATISFIES entity.id = '01f6a9eb-0d7e-4495-a90f-f96a38aef621'
                       AND entity.type='parent' END

Complex N1QL query in Coucbbase

1 Answers