CosmosDB Join (SQL API)

Question

I'm using CosmosDB using SQL API and I'm trying to join two collections. I saw join example within a document but not getting what actually looking.

RequestLog

{
"DateTimeStamp": "2018-03-16T10:56:52.1411006Z",
"RequestId": "8ce80648-66e2-4357-98a8-7a71e8b65301",
"IPAddress": "0.0.0.173"
}

ResponseLog

{
"DateTimeStamp": "2018-03-16T10:56:52.1411006Z",
"RequestId": "8ce80648-66e2-4357-98a8-7a71e8b65301",
"Body": "Hello"
}

Is it possible to join both collections? how?

Answer 1

Actually Cosmos DB JOIN operation is limited to the scope of a single document. What possible is you can join parent object with child objects under same document.

Cross-document joins are NOT supported, so you would have to implement such query yourself.

Answer 2

It is not possible to write join queries across multiple collections in Cosmos, or even across multiple documents in a single collection for that matter. Your only options here would be to issue separate queries (preferably in parallel) OR if your documents lived together in the same collection, you could retrieve all the relevant logs for a request using the common RequestId property.

SELECT * from c WHERE c.RequestId = '8ce80648-66e2-4357-98a8-7a71e8b65301'

This will only work if the object structure across the documents is the same. In this example it's possible because they both share a property of the same name called RequestId. You can't do JOIN on arbitrary properties.