N1QL Joins issue

SQL++
1

Hi,
I have just started using Couchbase 4.1 & am trying the JOINS.
I am not able to get data for Order & Order Details by using JOINS, the following are the json documents & N1QL used by me:
doc 1
{
“docType”:“orders”,
“orderId”:1
}
doc 2
{
“docType”:“orders”,
“orderId”:2
}
doc 3
{
“docType”:“orderDetails”,
“orderId”:1,
“orderItems” : [
{“itemName”:“item1”},
{“itemName”:“item2”}
]
}
doc 4
{
“docType”:“orderDetails”,
“orderId”:2,
“orderItems” : [
{“itemName”:“item3”},
{“itemName”:“item4”},
{“itemName”:“item5”}
]
}

N1QL
SELECT *
FROM devl_test AS orders
JOIN devl_test AS orderDets
ON KEYS orders.orderId
WHERE orders.docType = "orders"
AND orderDets.docType = “orderDetails”

I refered the existing posts & the above N1QL looked good, but I am not getting any results i.e. zero rows.

Thanks,
Sachin Vyas.

2

You need to do two things to make this work. First, change the “orderId” values to strings. The system is expecting the values used as keys to be strings.

Like this:
doc 4
{
“docType”:“orderDetails”,
“orderId”: “2”,
“orderItems” : [
{“itemName”:“item3”},
{“itemName”:“item4”},
{“itemName”:“item5”}
]
}

Second, reverse the order of the join, like this:
select * from devl_test as orderDets join devl_test as orders on keys orderDets.orderId
where orders.docType = “orders” and orderDets.docType = “orderDetails”

Finally, note that the join is on the key values of the documents, not the “docId” value in the documents. The design you have implies that the key of the first document is exactly “1” (without the quotes).

3

You can use TO_STRING(orderId) in your query to cast orderId to string.

4

Thanks for the reply johan_larson.
I made the changes to the data in json document - as suggested by you - made “orderId” as string in all 4 json documents
Also made changes to N1QL - as suggested by you, but still I get zero rows.
Updated N1QL
SELECT *
FROM devl_test AS orderDets
JOIN devl_test AS orders
ON KEYS orderDets.orderId
WHERE orders.docType = "orders"
AND orderDets.docType = “orderDetails”

Please note that I just created the json documents in the “devl_test” bucket, I haven’t done anything else eg index creation etc…am I missing anything?

Regards,
Sachin Vyas.

5

geraldss,
Thanks for your suggestion.

Regards,
Sachin Vyas.

1 Like
6

What are your actual document keys?

7

You can find the document keys using this query:

select meta().id from devl_test

8

The N1QL select meta().id from devl_test returns the following data for the 4 docs mentioned in my original query:
doc1
{
“id”: “Order1”
}

doc2
{
“id”: “Order2”
}

doc3
{
“id”: “OrderDetails1”
}

doc4 =
{
“id”: “OrderDetails2”
}

Regards,
Sachin Vyas.

9

OK, the keys you are using don’t match what what you are trying to join on.

You are trying to join doc 3, with field “orderId” equal to “1” to doc 1, with key “Order1”. Those two values need to match for the join to happen.

Change either the values in your orderDets.orderId fields or your document keys. Either way will work.

10

Thanks johan_larson…It works now.

Thanks & Regards,
Sachin Vyas.

1 Like