Querying Cosmos Containers Containing Items of Various Types

Querying Cosmos Containers Containing Items of Various Types

Problem

When querying Cosmos DB from C# code it’s convenient to use Linq. When you only want to get documents of one type, this is straightforward using GetItemLinqQueryable<T>().

When you want to get documents of multiple types, each having different properties but you want to restrict the returned documents by a condition that spans fields that are not common to all of these types, you’re in trouble.

Solution

It turns out that you can get the SQL query text by using something like

string queryText = client.GetContainer("...", "...")
    .GetItemLinqQueryable<SomeType>()
    .ToQueryDefinition().QueryText;

This will produce a query like:

SELECT VALUE root 
FROM root 

Notice the SELECT VALUE root. This means that the data returned is not projected into something that just has the properties of SomeType. Instead, the entire documents stored in cosmos DB are returned.

We can take advantage of this by giving SomeType all the properties we need for the query’s WHERE clause. This might be a union of properties of documents that have different types and hence different properties.

Example

Given the documents of types

record A(string OnlyInA, string FullyQualifiedType);
record B(string OnlyInB, string FullyQualifiedType);

and a type that has the union of properties we’d like to use in the query’s WHERE clause like

record WhatWeNeedFromAorB (string? OnlyInA, string? OnlyInB);

We can write a query like

string queryText = client.GetContainer("...", "...")
    .GetItemLinqQueryable<WhatWeNeedFromAorB>()
    .Where(o => o.OnlyInA == "foo" || o.OnlyInB == "bar")
    .ToQueryDefinition().QueryText;

The resulting query will be

SELECT VALUE root 
FROM root 
WHERE root["OnlyInA"] = "foo" OR root["OnlyInB"] = "bar"

We can then pass queryText to GetItemQueryIterator<JObject>() to get all the matching documents. While iterating through them, we can then deserialize each document to the appropriate type:

FeedIterator<JObject> iterator = await client
    .GetContainer("...", "...")
    .GetItemQueryIterator<JObject>(queryText);
        
while (iterator.HasMoreResults)
{
    foreach (JObject document in await query.ReadNextAsync())
    {
        string fullyQualifiedType = document["FullyQualifiedType"].Value<string>();
        Type type = Type.GetType(fullyQualifiedType);
        yield return document.ToObject(type);
    }
}