[SERVER-16456] Provide aggregation operations at each group level. Created: 08/Dec/14  Updated: 09/Dec/14  Resolved: 08/Dec/14

Status: Closed
Project: Core Server
Component/s: Aggregation Framework
Affects Version/s: 2.6.5, 2.8.0-rc2
Fix Version/s: None

Type: Improvement Priority: Minor - P4
Reporter: Tugdual Grall Assignee: Unassigned
Resolution: Done Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Participants:

 Description   

Suppose I have the following documents:

db.country.insert(
{
  "region" : "EMEA",
  "country" : "France",
  "count": 10
}
);
 
db.country.insert(
{
  "region" : "EMEA",
  "country" : "Germany",
  "count": 30
}
);
 
db.country.insert(
{
  "region" : "AMERICAS",
  "country" : "USA",
  "count": 15
}
);
 
db.country.insert(
{
  "region" : "AMERICAS",
  "country" : "Canada",
  "count": 20
}
);

I want to get the following stats out of this database:

  • Number of document per regions, per country
  • sum (or avg...) of the "count" for each region and country

AFAIK in MongoDB I need to use 2 aggregate calls:

// for Region
db.country.aggregate([
 { $group : { _id : "$region", doc_count : {$sum : 1} , total : { $sum : "$count" } } }
]);

result:

{ "_id" : "AMERICAS", "doc_count" : 2, "total" : 35 }
{ "_id" : "EMEA", "doc_count" : 2, "total" : 40 }

// For country inside a region

db.country.aggregate([
 { $group : { _id : { region : "$region" , country : "$country"  }, doc_count : {$sum : 1} , total : { $sum : "$count" } } }
]);

result:

{ "_id" : { "region" : "AMERICAS", "country" : "USA" }, "doc_count" : 1, "total" : 15 }
{ "_id" : { "region" : "AMERICAS", "country" : "Canada" }, "doc_count" : 1, "total" : 20 }
{ "_id" : { "region" : "EMEA", "country" : "Germany" }, "doc_count" : 1, "total" : 30 }
{ "_id" : { "region" : "EMEA", "country" : "France" }, "doc_count" : 1, "total" : 10 }

>> May be it is possible to do it with some "hacks" in the aggregation pipeline, but I have not found it yet.

It would be useful to group by firs level and have an option to create sub document for each sub element. To be honest I do not have think through the full process (developer experience, result, limitations)

This feature will make the life easier to developers to create dashboard for example



 Comments   
Comment by Tugdual Grall [ 09/Dec/14 ]

Thanks Asya, I just look stupid now

Comment by Asya Kamsky [ 08/Dec/14 ]

You can do this in a single aggregation - is your request for simpler syntax or for more efficient aggregation optimization of some sort?

db.country.aggregate([  { $group : { _id : { region : "$region" , country : "$country"  }, doc_count : {$sum : 1} , total : { $sum : "$count" } } },{$group:{_id:"$_id.region",grandcount:{$sum:"$doc_count"},grandtotal:{$sum:"$total"}, countries:{$push:{country:"$_id.country",doc_count:"$doc_count","total":"$total"}}}} ]).pretty()
{
	"_id" : "EMEA",
	"grandcount" : 2,
	"grandtotal" : 40,
	"countries" : [
		{
			"country" : "Germany",
			"doc_count" : 1,
			"total" : 30
		},
		{
			"country" : "France",
			"doc_count" : 1,
			"total" : 10
		}
	]
}
{
	"_id" : "AMERICAS",
	"grandcount" : 1,
	"grandtotal" : 15,
	"countries" : [
		{
			"country" : "USA",
			"doc_count" : 1,
			"total" : 15
		}
	]
}

Generated at Thu Feb 08 03:41:06 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.