No this is not supported. I would recommend you read the following articles on Cosmos DB so you understand the difference between a NoSQL and relational database and how best to model and partition data with in it. There are more docs you'll need to read of course too but these will help lay the groundwork as Cosmos is not a relational database and there is a learning curve associated with it.
Inner or left or right join between cosmos db collections
prasad k
86
Reputation points
Hi Team,
I am trying to join two different collections in cosmos DB. Is it possible to join two collections or containers.
If not Please advise is it possible with Table API or any other.
Thanks.
Accepted answer
-
Mark Brown - MSFT 2,761 Reputation points Microsoft Employee
2020-10-07T12:43:09.39+00:00
1 additional answer
Sort by: Most helpful
-
Daniel Barreto 1 Reputation point
2022-05-26T10:20:07.123+00:00 CosmosDB JOIN operation is limited to the scope of a single document. So, you can join parent object with child objects under same document.
Eg:
--Single query: Select * from c
Return these collections:
[ { "id": "863e778d-21c9-4e2a-a984-d31f947c665c", "name": "Sleeping Bag (6')", "tags": ["Any", "SomeValue" ,"Other"] }, { "id": "654dfd-65s5-65s4-9s8d-654asdf54safs5", "name": "Sleeping Bag (7')", "tags": null }, { "id": "6d5gf645-9879s-978sd-7s9s-98ds97s95s", "name": "Sleeping Bag (8')" } ]
In some cases, the objects or children are null or do not exist, then You can emulate LEFT JOIN with the check functions (IS_DEFINED or IS_NULL) AND EXISTS expression.
E.g:
--Single 'JOIN' query: SELECT c.name, c.tags FROM c JOIN d IN c.tags WHERE d = 'SomeValue' --Return only one element: [ { "name": "Vareno Sleeping Bag (6') Turmeric" } ] --Emulate 'LEFT JOIN' Query: SELECT c.name, c.tags FROM c WHERE ( --Like a "Left Join SomeElement" NOT IS_DEFINED(c.tags) OR c.tags = null OR EXISTS ( SELECT null FROM d IN c.tags WHERE d = 'SomeValue' ) ) --Return: [ { "name": "Sleeping Bag (6')", "tags": ["Any", "SomeValue" ,"Other"] }, { "name": "Sleeping Bag (7')", "tags": null }, { "name": "Sleeping Bag (8')", } ]