N1ql query for joining two types of document inside a single bucket


#1

How to join the following two documents using N1ql query which is inside the same bucket named “content”.
1>if document is of “publish type”.
select * from content where _type=“Publish”
output:-
[ {
“content”: {
“_id”: “3648df70-727b-4406-ab8a-0a7072a80ae3”,
“_type”: “Publish”,
“author”: “author_test”,
“book”: {
“$ref”: “3dd29d6e-1bcb-4d86-82c1-85e8ffee3ef8”,
“_type”: “Book”
},
“classifications”: [
“class1”,
“class2”
],
“comments”: “”,
“createdAt”: “2018-05-30T14:04:02.061Z”,
“description”: “Description Test”,
“features”: [],
“modifiedAt”: “2018-05-30T14:04:02.061Z”,
“name”: “Science”,
“organizationId”: “1234”,
“pageCount”: “”,
“price”: “500”,
“status”: “REQUESTED”,
“store”: “”,
“tags”: [
“tag1”,
“tag3”
],
“thumbnail”: “Test00”,
“title”: “”,
“userId”: “user1”,
“zipUrl”: “”
}
},
{
“content”: {
“_id”: “3c304802-84f6-40bd-9f0d-54f68ffdd3c3”,
“_type”: “Publish”,
“author”: “Sanjay1234”,
“book”: {
“$ref”: “5f92ba3a-a01e-4a37-9512-070195c39a31”,
“_type”: “Book”
},
“classifications”: [
“class1”,
“class2”
],
“comments”: “”,
“createdAt”: “2018-05-30T14:06:31.875Z”,
“description”: “Description abc”,
“features”: [],
“modifiedAt”: “2018-05-30T14:06:31.875Z”,
“name”: “History”,
“organizationId”: “1234”,
“pageCount”: “”,
“price”: “2000”,
“status”: “APPROVED”,
“store”: “”,
“tags”: [
“tag10”,
“tag3”
],
“thumbnail”: “Test87”,
“title”: “”,
“userId”: “user1”,
“zipUrl”: “”
}
},
{
“content”: {
“_id”: “5ca544da-d616-4927-9e5f-9f2153bcea95”,
“_type”: “Publish”,
“author”: “Sanjay”,
“book”: {
“$ref”: “5f92ba3a-a01e-4a37-9512-070195c39a31”,
“_type”: “Book”
},
“classifications”: [
“class1”,
“class2”
],
“comments”: “”,
“createdAt”: “2018-05-30T14:05:02.435Z”,
“description”: “Description Test”,
“features”: [],
“modifiedAt”: “2018-05-30T14:05:02.435Z”,
“name”: “History”,
“organizationId”: “1234”,
“pageCount”: “”,
“price”: “1000”,
“status”: “APPROVED”,
“store”: “”,
“tags”: [
“tag1”,
“tag3”
],
“thumbnail”: “Test87”,
“title”: “”,
“userId”: “user1”,
“zipUrl”: “”
}
},]
2>if document is of “Book” type.
select * from content where _type=“Book”
output:-
[{
“content”: {
“_id”: “3dd29d6e-1bcb-4d86-82c1-85e8ffee3ef8”,
“_type”: “Book”,
“active”: true,
“createdAt”: “2018-05-29T10:56:53.098Z”,
“desc”: “Science Book”,
“language”: “Arabic”,
“modifiedAt”: “2018-05-29T10:56:53.098Z”,
“name”: “Science”,
“organizationId”: “1234”,
“pages”: [],
“structureId”: “52”,
“thumbnail”: “Test00”,
“userId”: “user1”
}
},
{
“content”: {
“_id”: “5f92ba3a-a01e-4a37-9512-070195c39a31”,
“_type”: “Book”,
“active”: true,
“createdAt”: “2018-05-29T10:57:27.426Z”,
“desc”: “History Book”,
“language”: “Spanish”,
“modifiedAt”: “2018-05-29T10:57:27.426Z”,
“name”: “History”,
“organizationId”: “1234”,
“pages”: [],
“structureId”: “53”,
“thumbnail”: “Test87”,
“userId”: “user1”
}
},
{
“content”: {
“_id”: “6853f26f-8905-4150-9683-50154f8e82d6”,
“_type”: “Book”,
“active”: true,
“createdAt”: “2018-05-29T10:59:23.962Z”,
“desc”: “Chemistry Book”,
“language”: “English”,
“modifiedAt”: “2018-05-29T10:59:23.962Z”,
“name”: “Chemistry”,
“organizationId”: “1234”,
“pages”: [
{
“$ref”: “5717f6e1-78e3-47fd-b7ab-731315cf6830”,
“_type”: “BookPage”
},
{
“$ref”: “b7f342c1-098b-4cd7-ace9-d21fe4ee56f0”,
“_type”: “BookPage”
},
{
“$ref”: “c5ce3f2e-ff4d-4de6-92f7-8459217ed495”,
“_type”: “BookPage”
},
{
“$ref”: “8911b8b4-b1bc-48b0-aec3-7aff11ba4c94”,
“_type”: “BookPage”
},
{
“$ref”: “7155d62b-6b06-4033-8e25-60001186c7d1”,
“_type”: “BookPage”
}
],
“structureId”: “57”,
“thumbnail”: “Test_ch”,
“userId”: “user1”
}
},]

in “Book” type “_id” property is same as “$ref” property of field “book” in document type “Publish”,which is to be used for a join.

I tried many stuff,but facing problem while declaring a alias name for a document and anable to know what is “Key” while joining ?


#2

Use ANSI JOIN with 5.5 https://blog.couchbase.com/ansi-join-support-n1ql/
Pre 5.5 you need relation ship with document key and field name not between field names.
Please provide sample documents with document key
select META().id AS dockey, * from content where _type=“Publish”
select META().id AS dockey, * from content where _type=“Book”