CODEWITHSUNDEEP
azureazure-cosmosdbazure-cosmosdb-sqlapi
user1981275
user1981275

Reputation: 13372

Azure Cosmos DB aggregation over nested structure

I have an azure cosmos DB with documents as below

{
  "id": "id_1",
  "location": "location_1",
  "colorCounts": {
    "red" : 1,
    "blue": 0,
    "yellow": 1
  }
},
{
  "id": "id_2",
  "location": "location_1",
  "colorCounts": {
    "red" : 0,
    "blue": 0,
    "yellow": 0
  }
}

and want make a query that groups the results by location while averaging all the values in colorCounts. My result would look like this:

{
  "location": "location_1",
  "colorCounts": {
    "red" : 0.5,
    "blue": 0,
    "yellow": 0.5
  }
}

When I try to average over colorCounts:

SELECT c.id, c.location, AVG(c.colorCounts) FROM c GROUP BY c.location

I do not get any color counts. I can average over single colors, but I do not know how to average over the nested object colorCounts.

Upvotes: 0

Views: 356

Answers (1)

Aswin
Aswin

Reputation: 7126

Script:

select a.location,{"red" : a.red, "blue": a.blue
 ,"yellow": a.yellow} as colorCounts FROM
(SELECT c.location,avg(c.colorCounts.red) as red,
avg(c.colorCounts.blue) as blue,
avg(c.colorCounts.yellow) as yellow FROM c
GROUP  by c.location)a

I tried to repro this with the same sample input and got the required output.

Output:

[
{
"location": "location_1",
"colorCounts": {
"red": 0.5,
"blue": 0,
"yellow": 0.5
}
}
]

enter image description here

Upvotes: 1

Related Questions