Inner or left or right join between cosmos db collections

Question

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

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.

Answer

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')",
   }
]

Inner or left or right join between cosmos db collections

1 additional answer