.net Azure Cosmos SDK aggregate functions

Question

No matter what I try, I cannot get Max(), MaxAsync() or a direct SQL Query get to work in Microsoft.Azure.Cosmos 3.38.1.

If I run the following statement in the Cosmos DB Emulator

SELECT MAX(f.preisEuro) AS maxWert, MIN(f.preisEuro) AS minWert FROM fahrzeuge f

I get the expected result

[
    {
        "maxWert": 5000000,
        "minWert": 1273
    }
]

Neither this code works:

var query = new QueryDefinition(
    "SELECT max(f.preisEuro) as maxWert, min(f.preisEuro) as minWert FROM fahrzeuge f");

 using var setIterator = container.GetItemQueryIterator<dynamic>(
     query
     ,requestOptions: new QueryRequestOptions()
     {
         // PartitionKey = new PartitionKey(MagicValues.PartionKeyValue),
         MaxConcurrency = 1,
         // MaxItemCount = 1,
     }
 );
 var results = new List<dynamic>();
 while (setIterator.HasMoreResults)
 {
     var response = await setIterator.ReadNextAsync();
     Console.WriteLine($"Response: {response.StatusCode}");
     results.AddRange(response);
 }

Nor does this code:

var minPreis = await  container.GetItemLinqQueryable<Fahrzeug>()
            .Select(f => f.PreisEuro).MinAsync();

I tried upper/lowercasing of the variable names and min() and max() keywords in the SQL but all results I get in return are empty curly braces as Json or 0 for MinAsync() and MaxAsync()

I know that one solution would be to read the whole contaienr and calculate the values in a loop but if there are MinAsync(), MaxAsync() functions in Microsoft.Azure.Cosmos.Linq.CosmosLinqExtensions

Update / Solution

the underlying issue was that I had used a wrong container(name). Cosmos doesn't throw any error if that container exists but simply returns 0 for Min/Max or an empty result when using the SQL statement

Answer 1

.net Azure Cosmos SDK aggregate functions

Aggregate functions worked successfully in both QueryDefinition method and LINQ method. Also both the codes uses Microsoft.Azure.Cosmos 3.38.1 version. LINQ method uses Min() and Mix() as you can see in the code below. Install Newtonsoft.Json package in the LINQ code.

While executing query in Azure Cosmos DB Emulator by taking some sample data:

SELECT  MAX(f.preisEuro) AS maxWert, MIN(f.preisEuro) AS minWert FROM fahrzeuge f

Output:

[
    {
        "maxWert": 50000,
        "minWert": 25000
    }
]

Code with QueryDefinition method:

static async Task Main(string[] args)
    {
        var cosmosClient = new CosmosClient(EndpointUri, PrimaryKey);
        var database = cosmosClient.GetDatabase(DatabaseId);
        var container = database.GetContainer(ContainerId);

        var query = new QueryDefinition("SELECT max(f.preisEuro) AS maxWert, min(f.preisEuro) AS minWert FROM fahrzeuge f");
        var resultSet = container.GetItemQueryIterator<dynamic>(query);

        while (resultSet.HasMoreResults)
        {
            var response = await resultSet.ReadNextAsync();
            foreach (var item in response)
            {
                Console.WriteLine($"Max Value: {item["maxWert"]}, Min Value: {item["minWert"]}");
            }
        }
    }

Output:

Max Value: 50000, Min Value: 25000

Code with LINQ method:

public class Car
{
    [JsonProperty("id")]
    public string Id { get; set; }

    [JsonProperty("marke")]
    public string Marke { get; set; }

    [JsonProperty("preisEuro")]
    public double PreisEuro { get; set; }
}

class Program
{
    private const string EndpointUrl = "*****";
    private const string PrimaryKey = "******";
    private const string DatabaseId = "NewDb";
    private const string ContainerId = "newCont";

    static async Task Main(string[] args)
    {
        using (var client = new CosmosClient(EndpointUrl, PrimaryKey))
        {
            var database = client.GetDatabase(DatabaseId);
            var container = database.GetContainer(ContainerId);

            var query = container.GetItemLinqQueryable<Car>(true);

            var minPreis = query.Min(car => car.PreisEuro);
            var maxPreis = query.Max(car => car.PreisEuro);

            Console.WriteLine($"Minimum preisEuro: {minPreis}");
            Console.WriteLine($"Maximum preisEuro: {maxPreis}");
        }
    }
}

Output:

Minimum preisEuro: 25000
Maximum preisEuro: 50000