Cosmos DB Left Join

Author: Jamie Rees

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?

Originally Sourced from: https://stackoverflow.com/questions/57463395/cosmos-db-left-join