Cosmos DB Left Join

3
votes

All of the documentation for Cosmos DB and it looks like it only supports the JOINkeyword, which seems to be a sort of INNER JOIN.

I have the following query:

SELECT * FROM
(
    SELECT 
        DISTINCT(c.id),
        c.OtherCollection,
    FROM c
    JOIN s IN c.OtherCollection
)
AS c order by c.id

This works fine and returns the data of documents that have OtherCollection populated. But It obviously does not return any documents that do not have it populated.

The reason for the join is that sometimes I execute the following query (queries are built up from user input)

SELECT * FROM
(
    SELECT 
        DISTINCT(c.id),
        c.OtherCollection,
    FROM c
    JOIN s IN c.OtherCollection
    WHERE s.PropertyName = 'SomeValue'
)
AS c order by c.id

The question is how can I have a sort of LEFT JOIN operator in this scenario?

1
azureazure-cosmosdb

1 Answers

0
votes

CosmosDB JOIN operation is limited to the scope of a single document. What possible is you can join parent object with child objects under same document.

It is totally different from SQL Join query which supports across two/many tables.