CODEWITHSUNDEEP
azure-cosmosdbazure-cosmosdb-sqlapi
Steve Norwood
Steve Norwood

Reputation: 413

Azure Cosmos DB how to group by a range of values

I have a bunch of documents in my Cosmos database that have a numeric value that can range in value by quite a range. I am trying to perform a group by query that groups these values within a range that then provides how many are in each range.

For example, lets say I have 10 documents, and the values are as follows: 1, 1, 3, 4, 6, 7, 12, 28, 70, 120

I want to be able to group these so there are counts for: "less than 10", "between 10 and 100", and "more than 100". I have tried writing something like the below, but it hasn't been working:

SELECT COUNT(c.TestValue > 10),
COUNT(c.TestValue <= 10 AND c.TestValue < 100),
COUNT(c.TestValue <= 100)
FROM c 
GROUP BY c.TestValue > 10,
c.TestValue <= 10 AND c.TestValue < 100,
c.TestValue <= 100

Obviously this doesn't work, but I'm struggling on how to write the SQL to make this work. I've been doing this for some other group by queries that have been working well:

SELECT TestStringValue as groupedKey,
COUNT(1) as groupedValue
FROM c
GROUP BY TestStringValue

This has been returning results as this:

{
    "groupedKey": "Apples",
    "groupedValue": 10
}
...

In an ideal world I'd want the range result to look like the below, but I don't think it's possible:

{
    "groupedKey": "Less than 10",
    "groupedValue": 6
},
{
    "groupedKey": "Between 10 and 100",
    "groupedValue": 3
},
{
    "groupedKey": "More than 100",
    "groupedValue": 3
}

Upvotes: 0

Views: 1742

Answers (1)

NotFound
NotFound

Reputation: 6202

You could use the ternary operator in combination with the SUM function to count an expression:

SELECT 
    SUM(c.number < 10 ? 1 : 0) AS lt10,
    SUM(c.number >= 10 ? 1 : 0) AS ge10
FROM c

It works decent if you got a WHERE expression that limits the scope to a few documents, but quickly uses a ton of RU on bigger sets as it can't leverage the index.

That said; the strength of Cosmos lies in its parallel processing. You can easily snip your request in three seperate requests that barely use any request units and merge the results together.

Upvotes: 1

Related Questions