How to do a self join in SQL API?

Question

I have column with id, parent_id . I want to do a self join and check if id = parent_id. will i be able to do it?

Answer 1

Hi @Nithish Reddy Minupuri , welcome to Microsoft Q&A forum.

As rightly mentioned by you, self-joins don't work in Cosmos DB for different documents, so retrieving the result based on self-join for parent-child relationship across multiple joins will not be possible. However, as we already know both noSQL and relational databases work different.

For noSQL database like Azure Cosmos DB we can have de-normalized documents and that makes it easier to retrieve the entire dataset in single read operation. But again sometimes just de-normalizing the documents might not be the correct solution as in your case where parent-child could go on to any nested level and even to update one property we need to retrieve this heavy document which can further grow big. In these cases we need to split the document into multiple documents and provide a weak link or relationship among those documents. Now here we might need to make multiple database trip but load on read/write operations decrease significantly. I would highly recommend you to go through below article that explains the concept with examples:

Data modeling in Azure Cosmos DB

Coming to the model of your document, I tried to split it into multiple documents and linked them through the hierarchy:

//first doc  
{  
"id": "2",  
"Parent_id":1,  
"states": [],  
"Attributes":[],  
"heirarchy": [1]  
}  
  
//second doc  
{  
"id": "3",  
"Parent_id":2,  
"states": [],  
"Attributes":[],  
"heirarchy": [1,2]  
}  
  
//third doc  
{  
"id": "4",  
"Parent_id":3,  
"states": [],  
"Attributes":[],  
"heirarchy": [1,2,3]  
}  
  
//fourth doc  
{  
"id": "5",  
"Parent_id":4,  
"states": [],  
"Attributes":[],  
"heirarchy": [1,2,3,4]  
}  

If we need to find all the children of say parent 1, we can write below query:

SELECT * FROM c WHERE ARRAY_CONTAINS(c.heirarchy, 1)  

In this model, it becomes easier to retrieve individual documents to update as well with less load. Again we can refine the documents as per the requirements.

Referenced Article: Recursively query for all linked documents in CosmosDB

Please let us know if this helps or else we can discuss further.

----------

Please don't forgot to click on accept it as answer button wherever the information provided helps you. This can be beneficial to other community members as well.