How to query from two containers in Cosmos DB (SQL API)

Question

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.

Hesper

any reason why you have seaprated thees 2? since you are going for cosmosdb why dont you store in same document? – Coexist

@Coexist 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. – Hesper

Found this through Google search. No one has answered the OP question. Is it possible to query between two containers? And if so, how? – Fastening

Answer 1

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

Sempach

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. – Hesper

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. – Sempach

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. – Hesper

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. – Supersonic

Answer 2

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

Douglassdougy

Answer 3

it is easily possible if you have two conatiners in a single database . it depends on what language you use to create a api . you can create a api to connect two 2 database and then query the first result in 2nd database . you can also use graphql to create api and query container ,.

Whenever

Answer 4

Cross-Container queries are not supported by cosmosdb. Alternately you may access the containers as different entities in a console App then

Container container1 = client.GetContainer("DBname", "Container1"); Container container2 = client.GetContainer("DBname", "Container2"); Container container3 = client.GetContainer("DBname", "Container3");

then query for element in container1 then using the result; query for some thing in container2 and so on.

Abm

Answer 5

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.

Encipher

Will it join two separate containers. – Hesper

The subquery link provided only does joins within a single document. – Underplay