While using Azure Cosmos DB, we sometimes need to join data within a single item instead of joining data across entities and sets, as you would do in a relational database.
Let’s see a practical illustration to further clarify this idea. Assume that a JSON file contains two objects, one of which has the following structure:
Let's suppose that this data resides in a table named ‘test’, and our objective is to retrieve specific data nested within. When you want to create a JOIN query to retrieve data from our driver using other tools ex: DB Visualizer, the SQL syntax to execute a JOIN statement that includes multiple items in the container would be as follows:
Notably, the join query syntax differs from the relational database syntax. In this scenario, the usual ON clause is replaced with the IN clause, subtly changing how the join operation is carried out.
