I would like to use mongoDB's Aggregation Framework to run what in SQL would look a bit like:
SELECT SUM(A), B, C from myTable GROUP BY B, C;
The docs state:
You can specify a single field from the documents in the pipeline, a previously computed value, or an aggregate key made up from several incoming fields.
But it's unclear what 'an aggregate key made from several incoming fields' actually is?
My dataset is a bit like this:
[{ "timeStamp" : 1341834988666, "label" : "sharon", "responseCode" : "200", "value" : 10, "success" : "true"},
{ "timeStamp" : 1341834988676, "label" : "paul", "responseCode" : "200", "value" : 60, "success" : "true"},
{ "timeStamp" : 1341834988686, "label" : "paul", "responseCode" : "404", "value" : 15, "success" : "true"},
{ "timeStamp" : 1341834988696, "label" : "sharon", "responseCode" : "200", "value" : 35, "success" : "false"},
{ "timeStamp" : 1341834988166, "label" : "paul", "responseCode" : "200", "value" : 40, "success" : "true"},
{ "timeStamp" : 1341834988266, "label" : "paul", "responseCode" : "404", "value" : 99, "success" : "false"}]
My query looks like this:
resultsCollection.aggregate(
{ $match : { testid : testid} },
{ $skip : alreadyRead },
{ $project : {
timeStamp : 1 ,
label : 1,
responseCode : 1 ,
value : 1,
success : 1
}},
{ $group : {
_id : "$label",
max_timeStamp : { $timeStamp : 1 },
count_responseCode : { $sum : 1 },
avg_value : { $sum : "$value" },
count_success : { $sum : 1 }
}},
{ $group : {
?
}}
);
My instinct was to try to pipe the results through to a second group, I know you can do this but it won't work because the first group already reduces the dataset too much and the required level of detail is lost.
What I want to do is group using label
, responseCode
and success
and get the sum of value from the result. It should look a bit like:
label | code | success | sum_of_values | count
sharon | 200 | true | 10 | 1
sharon | 200 | false | 35 | 1
paul | 200 | true | 100 | 2
paul | 404 | true | 15 | 1
paul | 404 | false | 99 | 1
Where there are five groups:
1. { "timeStamp" : 1341834988666, "label" : "sharon", "responseCode" : "200", "value" : 10, "success" : "true"}
2. { "timeStamp" : 1341834988696, "label" : "sharon", "responseCode" : "200", "value" : 35, "success" : "false"}
3. { "timeStamp" : 1341834988676, "label" : "paul", "responseCode" : "200", "value" : 60, "success" : "true"}
{ "timeStamp" : 1341834988166, "label" : "paul", "responseCode" : "200", "value" : 40, "success" : "true"}
4. { "timeStamp" : 1341834988686, "label" : "paul", "responseCode" : "404", "value" : 15, "success" : "true"}
5. { "timeStamp" : 1341834988266, "label" : "paul", "responseCode" : "404", "value" : 99, "success" : "false"}