Checking Data in One Cosmos DB Container Before Inserting Into Another

Question

I'm working with Azure Cosmos DB and have a scenario where I need to check for data in one container (e.g., ContainerA) before inserting data into another container (e.g., ContainerB).

I initially considered using stored procedures for this purpose, but I encountered the following error:

Function is not allowed to operate on resources outside current collection

It appears that stored procedures in Cosmos DB can't operate across multiple collections.

Could someone suggest alternative ways to achieve this within the database itself? I'm aiming to avoid handling this logic in client-side code.

Any help or pointers would be greatly appreciated!

Correct: stored procedures are scoped to a partition within a container. There are no cross-container operations in stored procedures (note: this is all documented). You'll need to do this yourself with multiple database calls. How you do this is up to you and your specific data schema, partitioning scheme, etc. (also, this really isn't "client-side" unless you're embedding database code in a client-side app) — David Makogon
– David Makogon, Commented Jan 17 at 1:25

Pavan K · Accepted Answer · 2025-01-17 14:29:24Z

-1

You can't query multiple containers in a single query in cosmos. The best way to do this without writing any client code is to merge the data in those 2 collections and differentiate each entity by some attribute like type . Then you can write a stored procedure.

answered Jan 17 at 14:29

Pavan K

11 bronze badge

Sign up to request clarification or add additional context in comments.

3 Comments

David Makogon Jan 17 at 15:30

How is the best way to"merge the data"? And what does that specifically mean? Also, how does a stored procedure help? Note that I already pointed multi-container query issues in my comment - there's no way to answer this question objectively (which is why I posted as a comment, not an answer)

Pavan K Jan 20 at 10:07

STEP 1 -> Move the data from these 2 different containers into single one and differentiate them with TYPE attribute (type1, type2) STEP 2 -> Write a stored procedure that checks if entity of type1 exists before inserting type2 You can hate this design pattern but it works. REF : stackoverflow.com/questions/70043211/… Also what is this ? "How is the best way to"merge the data"?" . It doesn't make any sense to me atleast

David Makogon Jan 20 at 14:56

Combining data from two containers to one is not a practical solution, especially if there are TB of PB of data, or if the two sets of data are partitioned differently, or if there is a legit reason for having separate containers. And again... it's not the "best" way - there is no "best way" except for what the OP deems "best". Maybe it's to create a materialized view specifically for this type of query? Or maybe the "best" way is to just keep a two-query model?

Collectives™ on Stack Overflow

Checking Data in One Cosmos DB Container Before Inserting Into Another

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related