N1QL Left join second layer object

I would to know that it is possible left join to second layer object?

User:
{
“id”: “424c4ade-158f-45db-aacc-c45c6d0d0987”,
“key”: “user_424c4ade-158f-45db-aacc-c45c6d0d0987”,
“type”: “user”,
“name”: “Tim Lee”,
“logo”: {
“createBy”: “user_424c4ade-158f-45db-aacc-c45c6d0d0987”,
“createDate”: “2018-04-16T16:26:34.806365+00:00”,
"fileKey": “file_e3c1c0b0-2bea-4b17-896f-583ef58ea12c”,
“height”: 953,
“offsetX”: 0,
“offsetY”: 0,
“updateBy”: “user_424c4ade-158f-45db-aacc-c45c6d0d0987”,
“updateDate”: “2018-04-16T16:26:34.8065315+00:00”,
“width”: 960
}
}

File:
{
“createBy”: “user_424c4ade-158f-45db-aacc-c45c6d0d0987”,
“createDate”: “2018-04-16T16:26:33.3368503+00:00”,
“deleteBy”: null,
“deleteDate”: “0001-01-01T00:00:00+00:00”,
“extension”: “.jpg”,
“fileName”: “767c6667-2762-4682-b15d-fb07f645ca52.jpg”,
“id”: “e3c1c0b0-2bea-4b17-896f-583ef58ea12c”,
"key": “file_e3c1c0b0-2bea-4b17-896f-583ef58ea12c”,
“length”: 83317,
“name”: “18664514_919103101563532_2104106691757902132_n.jpg”,
“type”: “file”,
“updateBy”: “user_424c4ade-158f-45db-aacc-c45c6d0d0987”,
“updateDate”: “2018-04-16T16:26:33.3371148+00:00”
}

Expected Result:
{
“id”: “424c4ade-158f-45db-aacc-c45c6d0d0987”,
“key”: “user_424c4ade-158f-45db-aacc-c45c6d0d0987”,
“type”: “user”,
“name”: “Tim Lee”,
“logo”: {
“createBy”: “user_424c4ade-158f-45db-aacc-c45c6d0d0987”,
“createDate”: “2018-04-16T16:26:34.806365+00:00”,
“fileKey”: “file_e3c1c0b0-2bea-4b17-896f-583ef58ea12c”,
“height”: 953,
“offsetX”: 0,
“offsetY”: 0,
“updateBy”: “user_424c4ade-158f-45db-aacc-c45c6d0d0987”,
“updateDate”: “2018-04-16T16:26:34.8065315+00:00”,
“width”: 960,
"file": {
“createBy”: “user_424c4ade-158f-45db-aacc-c45c6d0d0987”,
“createDate”: “2018-04-16T16:26:33.3368503+00:00”,
“deleteBy”: null,
“deleteDate”: “0001-01-01T00:00:00+00:00”,
“extension”: “.jpg”,
“fileName”: “767c6667-2762-4682-b15d-fb07f645ca52.jpg”,
“id”: “e3c1c0b0-2bea-4b17-896f-583ef58ea12c”,
“key”: “file_e3c1c0b0-2bea-4b17-896f-583ef58ea12c”,
“length”: 83317,
“name”: “18664514_919103101563532_2104106691757902132_n.jpg”,
“type”: “file”,
“updateBy”: “user_424c4ade-158f-45db-aacc-c45c6d0d0987”,
“updateDate”: “2018-04-16T16:26:33.3371148+00:00”
}
}
}

Assuming u.logo.fileKey is document key of File

SELECT u, f 
FROM `User` AS u LEFT JOIN `File` AS f ON KEYS u.logo.`fileKey`;

Pre 5.5.0
https://dzone.com/articles/visually-explaining-n1ql-joins

In 5.5.0 onwards you can use ANSI JOI syntax
https://blog.couchbase.com/ansi-join-support-n1ql/

file cannot be inside logo?

SELECT u.*, OBJECT_ADD(u.logo,"file",f) AS logo
FROM `User` AS u LEFT JOIN `File` AS f ON KEYS u.logo.`fileKey`;
1 Like

It is work! thank you very much :heart_eyes: