How do I do joins (or equivalent)?

[KevinMallinson]

I would like to know if joins are possible, for example, a sample scenario:

• Client can have multiple Reservations.
• These are two documents (one for client, one for reservation)
• Reservations are read and write heavy, and there can be thousands of them, so don't want to nest it inside the client.

I haven't found anywhere in the docs that specifies how I can get the client, and join each reservation, in a single query to Azure.

I can use my Cosmos Store for Reservation to match all reservations I need, and I can use my other Cosmos Store for Client to match the proper client.

But how do I do this in a single trip? I don't want to have to do multiple round trips - as this increases the time it takes to do the task.

Does Cosmonaut support this workflow? It is equivalent to what I would do in SQL, e.g.

SELECT * FROM Client c
INNER JOIN Reservation r
ON c.ID = r.ClientID

I can't figure out how to do this in Cosmonaut.

Please let me know if this is possible.

Thanks.

[Mortana89]

It's not possible neither in Cosmonaut nor in Cosmos DB. Cosmos DB is not a relational database, there is one very important limitation, you cannot do cross-joins. This is an issue we faced a lot as well, but you get used to it, and you start modelling your data better and better, for optimal performance. In our case, we embed a few key elements of the foreign object (e.g. client id and client name) into the reservation object. This allows us to show the required data in e.g. a list or whatever. Would a client name change, an event is thrown that will be catched by another microservice, updating the reservations of this client.

You should see if you can achieve the similar approach. If you have special ranges on a client that you need, that you would use to filter on a reservation, it might be worthwile to move that data to reservation. Storage space is cheaper than performance...

[Elfocrash]

Hello @KevinMallinson,

As @Mortana89 said, this isn't a Cosmonaut limitation but a Cosmos DB one. Cosmos DB does not support cross document joins. There is no way you can achieve something like this in a single trip no matter the SDK. The solution explained by @Mortana89 is the only one I can see.

[KevinMallinson]

Thank you for the information.