Cosmos DB Left Join

Question

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?

Answer 1

Maybe a little late, but it work for me:

FROM
    a
    JOIN b in (SELECT Value(IIF(IS_NULL(a.collection) OR NOT IS_DEFINED(a.collection) OR (ARRAY_LENGTH(a.collection) = 0),[{}], a.collection)))

EDIT: Explanation:

When we join an array property and it doesn't exist or is null or has not elements, the document is excluded. Acts as SQL Inner join. So what we check for this conditions:

 (IIF(IS_NULL(a.collection) OR NOT IS_DEFINED(a.collection) OR (ARRAY_LENGTH(a.collection) = 0))

if true, the join is made with an array with a single empty element "[{}]", so the parent document will be returned only once for this join and with array property selected to null value. If condition is false, the join is made with the array property normally

So, this query

SELECT 
   a.id as id,
   a.value as value,
   b.id as bId,
   b.value as bValue
FROM
    a
    JOIN b in (SELECT Value(IIF(IS_NULL(a.collection) OR NOT IS_DEFINED(a.collection) OR (ARRAY_LENGTH(a.collection) = 0),
[{}], a.collection)))
WHERE IS_NULL (a.collection)

return

[{ "id": 100,
  "value": "a property value"
},
....
]

If you use normal join, you get an empty result

SELECT 
   a.id as id,
   a.value as value,
   b.id as bId,
   b.value as bValue
FROM
    a
    JOIN b in a.collection
WHERE IS_NULL (a.collection)

return

[]

If you want to get b propeties with default or null values, only change [{}] by [{Default properties values}]

SELECT 
   a.id as id,
   a.value as value,
   b.id as bId,
   b.value as bValue
FROM
    a
    JOIN b in (SELECT Value(IIF(IS_NULL(a.collection) OR NOT IS_DEFINED(a.collection) OR (ARRAY_LENGTH(a.collection) = 0),
[{"id": 0, "value": null}], 
a.collection)))
WHERE IS_NULL (a.collection)

return

[{ "id": 100,
  "value": "a property value",
  "bId": 0,
  "bValue": null
},
....
]