How to query nested properties in Cosmos DB from .NET 8 API

Question

How to query nested properties in Cosmos DB from .NET 8 API?

When I try to retrieve nested properties with this code, CarModel is null:

public async Task<object> GetDriverByIdAsync6(string id)
{
    var queryDefinition = new QueryDefinition($"SELECT c.DriverNumber, c.Name, c.CarModel.Make, c.CarModel.SomeNumber FROM c WHERE c.id = '{id}'");

    var queryResult = await _container.GetItemQueryIterator<Driver>(queryDefinition).ReadNextAsync();

    var driver = queryResult.FirstOrDefault();

    return new
    {
        DriverNumber = driver.DriverNumber,
        Name = driver.Name,
        Make = driver.CarModel.Make,
        SomeNumber = driver.CarModel.SomeNumber
    };
}

If I try to return the whole CarModel like this, it returns it properly, and I can then filter each nested property, but I don't want to select the whole CarModel from the database:

public async Task<object> GetDriverByIdAsync5(string id)
{
    var queryDefinition = new QueryDefinition($"SELECT c.DriverNumber, c.Name, c.CarModel FROM c WHERE c.id = '{id}'");

    var queryResult = await _container.GetItemQueryIterator<Driver>(queryDefinition).ReadNextAsync();

    var driver = queryResult.FirstOrDefault();

    return new
    {
        DriverNumber = driver.DriverNumber,
        Name = driver.Name,
        Make = driver.CarModel.Make,
        SomeNumber = driver.CarModel.SomeNumber
    };
}

Is there a way to query specific nested properties without selecting whole object and then filter it ?

This is how the data looks like in Cosmos DB:

{
    "id": "4",
    "Name": "William",
    "CarModel": {
        "Make": "Mercedess",
        "Transmission": "Manual",
        "SomeNumber": 145
    },
    "DriverNumber": 12
}

Answer 1

Using your query you retrieve an answer looking like this:

[
    {
        "DriverNumber": 12,
        "Name": "William",
        "Make": "Mercedess",
        "SomeNumber": 145
    }
]

Then you try to deserialize into the same class model as your original data even though you've projected it into a different structure. If you use a class that fits the resulting structure it should yield the properties as you want:

//use this for in GetItemQueryIterator<DriverSummary>
public class DriverSummary
{
    public int DriverNumber { get; set; }
    public string Name { get; set; }
    public string Make { get; set; }
    public int SomeNumber { get; set; }
}

Alternatively to give some extra insights you could also edit your query and only return the properties you'd like while keeping the same class using:

SELECT 
  c.DriverNumber, 
  c.Name, 
  { 'Make': c.CarModel.Make, 'SomeNumber': c.CarModel.SomeNumber } AS CarModel 
FROM c 
WHERE c.id = '4'