N1QL Joins on same bucket but with different type of document

SQL++
1

Hi i am trying to implement joins in N1QL in DP 4 using .NET API, below is my document structure

Document 1 :

{
“userKey”: “User_87”,
“type”: “UserAssignments”,
“assignments”: [
{
“assignmentKey”: “aSqT7HzxM/+xMI+rkC+m8g==”
},
{
“assignmentKey”: “cqXuVksH162WV68F1ybawQ==”
}
]
}

Document 2 :

{
“userKey”: “User_88”,
“type”: “UserAssignments”,
“assignments”: [
{
“assignmentKey”: “YEN7Rl30xvyofUh05+xHIA==”
},
{
“assignmentKey”: “/OYYo+HhMZmTqEPWXhhwiw==”
}
]
}

Document 3 :

{
“userKey”: “User_88”,
“name”: “testUser1”,
“type”: “UserInfo”
}

Here i am trying to join “UserAssignments” type document with “UserInfo”

Below is my n1ql query,

select * from sample1 UserAssignments JOIN sample1 UserInfo ON KEYS (UserAssignments.userKey)
WHERE UserAssignments.type=“UserAssignments” AND UserInfo.type=“UserInfo”

I get { “results”: [] } as output.

Can we add subquery as an datasource in from e.g. select * from (select userKey from sample1) Test1 ?

Any help in JOINS would really help.

Thanks,
Amalraj

2

Thank you for your question. We are working to improve the documentation around JOINS. The Join syntax works as follows:

Say you have two documents. One of them needs to contain a field whose value contains the Couchbase KV key of the other document:

“keyA” is the Couchbase KV key for Doc A.

Doc A: {
some fields
}

Doc B: {
some fields
“joinField”: “keyA"
}

SELECT * FROM default b JOIN default a ON KEYS b.joinField

NOTE: In your example documents, if you’re trying to retrieve a specific list of assignments it might be helpful to UNNEST the assignments array prior to your application consuming the results.

Thanks

Todd

3

Hi Amalraj,

Per Todd’s response, your UserAssignments documents would need to contains the id’s of the corresponding UserInfo documents. That would enable you to do the JOIN you’ve described. ON KEYS refers to the field in the left-hand side document that contains the id’s of the corresponding right-hand side documents.

Please reply here if this needs more clarification.

To your second question, the answer is yes. Support for subqueries in the FROM clause has been implemented after DP4. It will be available in our integrated Couchbase Server 4.0 Developer Preview, scheduled for release next month (April).

Thanks,
Gerald

4

Thanks Todd and Gerald I was able to get the join working with your help. If support for subqueries in the FROM clause would be available in April how would joins work if we have to JOIN two subqueries results in FROM as subqueries may have a different structure. Is Couchbase KV key (id) necessary for joins, can any other values of the documents e.g “userKey” be used for joins ?

Thanks,
Amalraj

5

Hi Amalraj,

Yes, the Couchbase KV key (id) is required for joins. No other fields or values can be used for joins.

When joining with subqueries, only the left-hand side of the join can contain a subquery. The right-hand side must directly reference a bucket. Please post any additional questions and feedback.

Thanks,
Gerald

6

what do u mean by "Couchbase KV key " …can you please expalin…

Thanks,
Vikas

7

Every document in Couchbase has an external key that is used for key-value lookup. That is what I am referring to.