Category: Tech Notes

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);