CODEWITHSUNDEEP
azure-cosmosdb
Abhishek Das
Abhishek Das

Reputation: 82

How to perform join operation similar to SQL join in Azure DocumentDB

I have a collection in Azure DocumentDB where in I have documents clustered into 3 sets using a JSON property called clusterName for each document. The 3 clusters of documents are templated somewhat like these:

{ "clusterName": "CustomerInformation", "id": "CustInfo1001", "custName": "XXXX" },

{ "clusterName": "ZoneInformation", "id": "ZoneInfo5005", "zoneName": "YYYY" },

{ "clusterName": "CustomerZoneAssociation", "id": "CustZoneAss9009", "custId": "CustInfo1001", "zoneId": "ZoneInfo5005" }

As you can see the document for CustomerZoneAssociation links the documents of CustomerInformation and ZoneInformation with their Id s. I need help in querying out information from CustomerInformation and ZoneInformation cluster with the help of their Id s associated in the CustomerZoneAssociation cluster. The result of the query I am expecting is:

{ "clusterName": "CustomerZoneAssociation", "id": "CustZoneAss9009", "custId": "CustInfo1001", "custName": "XXXX", "zoneId": "ZoneInfo5005", "zoneName": "YYYY" }

Please suggest a solution which would take only 1 trip to DocumentDB

Upvotes: 3

Views: 8862

Answers (1)

Andrew Liu
Andrew Liu

Reputation: 8119

DocumentDB does not support inter-document JOINs... instead, the JOIN keyword is used to perform intra-document cross-products (to be used with nested arrays).

I would recommend one of the following approaches:

  • Keep in mind that you do not have to normalize every entity as you would with a traditional RDBMS. It may be worth revisiting your data model, and de-normalize parts of your data where appropriate. Also keep in mind that, de-normalizing comes with its own trade offs (fanning out writes vs issuing follow up reads). Check out the following SO answer to read more on the tradeoffs between normalizing vs de-normalizing data.

  • Write a stored procedure to batch a sequence of operations within a single network request. Checkout the following SO answer for a code sample on this approach.

Upvotes: 8

Related Questions