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?

Destruct

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.

Sheepcote

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.

Fact

"preferably in parallel" Can you please explain how do you do that in Cosmos DB SQL Api. – Buckjump

Answer 3

In Azure Cosmos DB, joins are scoped to a single item. Cross-item and cross-container joins are not supported. check documentation here

Foreignborn