5

I am new to cosmos db. I chose cosmos db (core sql), created a database having two containers say EmployeeContainer and DepartmentContainer. Now I want to query these two container and want to fetch employee details with associated department details. I stuck on a point and need help.

Below is the structure of my containers.
EmployeeContainer : ID, Name, DepartmentID
DepartmentContainer: ID, Name

Thanks in advance.

nrofis
  • 8,975
  • 14
  • 58
  • 113
Rahul Jha
  • 874
  • 1
  • 11
  • 28
  • any reason why you have seaprated thees 2? since you are going for cosmosdb why dont you store in same document? – Sajeetharan Jun 22 '20 at 10:43
  • @Sajeetharan I did it maintain a relation between emp and dept. I was trying same thing as we do in SQL. In single document we can query using join but I did not find a way to query two different container. Any idea. – Rahul Jha Jun 22 '20 at 11:24
  • Found this through Google search. No one has answered the OP question. Is it possible to query between two containers? And if so, how? – Francisco d'Anconia Oct 11 '22 at 19:16

3 Answers3

22

Cosmos DB is not a relational database. You do not store different entities in different containers if they are queried together. They are either embedded in other entities or stored as separate rows using a shared partition key with other entities in the same container.

Before you get too far with Cosmos you need to understand how to model and partition data to ensure the best possible performance. I strongly recommend you read the docs on partitioning and specifically read these docs below.

Data modeling in Cosmos DB

Partitioning in Cosmos DB

How to model and partition data - a real world example

And watch Data Modeling in Cosmos DB - What every relational developer should know

Mark Brown
  • 8,113
  • 2
  • 17
  • 21
  • do you want to say that I should keep master data in one document and relational data in other documents and each document should reside in same container. Is that so. In that case I don't see use of multiple containers. I was trying to keep all master data like Department in one container and all relational data in other container to keep separate things separate and then query from two containers using some relation like we do in other databases like SQL, Oracle. – Rahul Jha Jun 26 '20 at 11:49
  • 1
    It is impossible to answer your question without understanding your application more deeply. You just need to ask yourself how you access the data most frequently. If you access department master data when a form or screen is loaded along with other master data then maybe you should store all that data together in a single container and query it together. btw, you can also keep this data in multiple places and then use Change Feed to keep "referential integrity" between data stored in multiple places. Hope that helps. – Mark Brown Jun 27 '20 at 20:59
  • Let me think of this approach but for this I have to reframe everything. I was thinking of a solution where all master data to be kept in one container and all related data in separate container. But it seems that cosmos have few limitations or you can say different way to approach things. But by the way then why there is second container, is it only to keep things separate but having no relation indeed. – Rahul Jha Jul 01 '20 at 11:07
  • TL;DR: you don't join containers. You would query each container individually, retrieve both results and then combine them e.g. in your notebook or application outside of the database. The point of having different containers is to store different datasets for specific queries which can be answered with all the data in a single container. If your main query (which you use most) requires data from both containers, you should restructure your data into a single container. – Cribber Dec 29 '21 at 09:50
0

It completely depends on the type of data you are trying to model. Generally, it comes down to relationships. 1:1 or 1:few often are best for embedding related items or where queries are updated together. 1:many or many:many for referencing related items are queried or updated independently.

For great talks on these issues check out https://www.gotcosmos.com/conf/ondemand

A. Wentzel
  • 484
  • 8
  • 16
-2

You can use subquery.

https://learn.microsoft.com/en-us/azure/cosmos-db/sql-query-subquery#mimic-join-with-external-reference-data

But this may consumes a lot of RU. And only inner join for now.

decoy
  • 151
  • 8