question

nithishreddyminupuri-8171 avatar image
0 Votes"
nithishreddyminupuri-8171 asked AnuragSharma-MSFT edited

How to do a self join in SQL API?

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?

azure-cosmos-db
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I I understood that SQL API doesn't support cross -item join. How do you resolve this problem. I can denormalize my data but that will have lot of updates and there can be multiple products under single product . My data atleast has 100-250 products in one denormalized document. As showed in below example it has multiple parent- child hierarchy .
{
"id": 1,
"Parent_id":2
"states": [],
"Attributes":[],
"Items": [
{
"id": 3,
"Parent_id":1,
"states": [],
"Attributes":[],
"Items": [
"id": 4,
"Parent_id":3,
"states": [],
"Attributes":[],
"Items": []
]
},
{
"id": 5,
"Parent_id":1,
"states": [],
"Attributes":[],
"Items": [
"id": 6,
"Parent_id":5,
"states": [],
"Attributes":[],
"Items": []
]
}
]
}

{

0 Votes 0 ·

1 Answer

AnuragSharma-MSFT avatar image
0 Votes"
AnuragSharma-MSFT answered AnuragSharma-MSFT edited

Hi @nithishreddyminupuri-8171, 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 136704-image.png wherever the information provided helps you. This can be beneficial to other community members as well.



image.png (7.3 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.