I have an aggregation query to count documents grouped by a property (this is actually part of my http://www.spotviz.info app that I’m working on – the query retrieves counts per Amateur Radio callsign for number of spots uploaded).
By default the result are not in any particular order (that I can see, but maybe I don’t enough enough test data to be able to tell), so I wanted to add a $sort condition.
Here’s the initial aggregation query:
db.Spot.aggregate(
[
{ $group : { _id : {"spotterCallsign" : "$spotter"},
count : {$sum : 1},
firstSpot : {$min : "$spotReceivedTimestamp"},
lastSpot : {$max : "$spotReceivedTimestamp"} }
}
] )
To add the $sort to the aggregation pipeline, just add another document for $sort following the $group, like this:
{ $sort : { "count" : -1 } }
The full query now looks like:
db.Spot.aggregate(
[
{ $group : { _id : {"spotterCallsign" : "$spotter"},
count : {$sum : 1},
firstSpot : {$min : "$spotReceivedTimestamp"},
lastSpot : {$max : "$spotReceivedTimestamp"} }
},
{ $sort : { "count" : -1 } }
] )
Building this query with the Java API is easy, just add another DBObject for the $sort document to the List containing all docs ($group, $sort), in the pipeline:
DBCollection col = db.getCollection("Spot");
// $group
DBObject groupFields = new BasicDBObject("_id", "$spotter");
groupFields.put("firstSpot", new BasicDBObject("$min", "$spotReceivedTimestamp"));
groupFields.put("lastSpot", new BasicDBObject("$max", "$spotReceivedTimestamp"));
groupFields.put("totalSpots", new BasicDBObject("$sum", 1));
DBObject group = new BasicDBObject("$group", groupFields);
List<DBObject> pipeline = Arrays.asList(group,
new BasicDBObject("$sort", new BasicDBObject("totalSpots", -1)));
AggregationOutput output = col.aggregate(pipeline);
