mongodb sort result of aggregate query and display day name

Question

I have the following aggregation query

db.orders.aggregate( 
        { "$project" : 
            { _id : 0, "dateDay" : 
                {"$concat" : [ 
                    {"$substr" : [{"$dayOfMonth" : "$timestamp"}, 0, 2]}, "-",
                    {"$substr" : [{"$month" : "$timestamp"}, 0, 2]}, "-",
                    {"$substr" : [{"$year" : "$timestamp"}, 0, 4]}
                ] }
            }
        },
        { "$group" :
            { "_id" : "$dateDay", "Count" : { "$sum" : 1 } } 
        },
        { $sort : { "Count : 1 } } 
    );

which returns something like:

        {
            "_id" : "3-9-2014",
            "Count" : 81
        },
        {
            "_id" : "14-12-2014",
            "Count" : 10
        },
        {
            "_id" : "2-9-2014",
            "Count" : 98
        },
        {
            "_id" : "1-9-2014",
            "Count" : 118
        },
        {
            "_id" : "20-8-2014",
            "Count" : 79
        },
        {
            "_id" : "8-6-2015",
            "Count" : 128
        }
    ],
    "ok" : 1
}

How do I sort the result by order of Day-Month-Year?

{ $sort : { "Count : 1 } } - gives me the sort by Count, but I like something like the MySQL query:

mysql> SELECT DATE(stamp), DAYNAME(stamp), COUNT(*) FROM user_orders WHERE DATE(stamp) >= DATE('2015-05-29') AND DATE(stamp) <= DATE('2015-06-19') GROUP BY DATE(stamp);
+-------------+----------------+----------+
| DATE(stamp) | DAYNAME(stamp) | COUNT(*) |
+-------------+----------------+----------+
| 2015-05-29  | Friday         |       63 |
| 2015-05-30  | Saturday       |        9 |
| 2015-05-31  | Sunday         |       11 |
| 2015-06-01  | Monday         |       94 |
| 2015-06-02  | Tuesday        |       92 |
| 2015-06-03  | Wednesday      |      109 |
| 2015-06-04  | Thursday       |       89 |
| 2015-06-05  | Friday         |       68 |
| 2015-06-06  | Saturday       |        9 |
| 2015-06-07  | Sunday         |       11 |
| 2015-06-08  | Monday         |      125 |
| 2015-06-09  | Tuesday        |      101 |
| 2015-06-10  | Wednesday      |       97 |
| 2015-06-11  | Thursday       |      110 |
| 2015-06-12  | Friday         |       85 |
| 2015-06-13  | Saturday       |       13 |
| 2015-06-14  | Sunday         |        6 |
| 2015-06-15  | Monday         |      113 |
| 2015-06-16  | Tuesday        |      109 |
| 2015-06-17  | Wednesday      |      125 |
| 2015-06-18  | Thursday       |       77 |
| 2015-06-19  | Friday         |       81 |
+-------------+----------------+----------+
22 rows in set (0.05 sec)

Any advice much appreciated

Answer 1

Using https://github.com/gabrielelana/mongodb-shell-extensions

> moment.last(10).days().forEach('day', function(m) {print(m.format('DD-MM-YYYY') + ' ' + m.format('dddd') + ': ' + db.orders.count({timestamp: moment.$inDay(m)}))});
18-06-2015 Thursday: 39
19-06-2015 Friday: 36
20-06-2015 Saturday: 0
21-06-2015 Sunday: 1
22-06-2015 Monday: 76
23-06-2015 Tuesday: 23
24-06-2015 Wednesday: 1
25-06-2015 Thursday: 5
26-06-2015 Friday: 2
27-06-2015 Saturday: 0
28-06-2015 Sunday: 0

Answer 2

Instead of having the first pipeline stage as the $project operator step, you could have the $group pipeline step that has the date aggregation operators as the group key expression. The following $sort operator pipeline would then sort on those keys in the order Day-Month-Year, like in this example:

db.orders.aggregate([    
    { 
        "$group": { 
            "_id": {
                "day": { "$dayOfMonth" : "$timestamp" },
                "month": { "$month" : "$timestamp" },
                "year": { "$year" : "$timestamp" }
            }, 
            "Count": { "$sum" : 1 } 
        } 
    },
    { 
        "$sort": { 
            "_id.year": 1,
            "_id.month": 1,
            "_id.day": 1 
        } 
    },
    { 
        "$project": { 
            "_id": 0, 
            "dateDay": {
                "$concat": [ 
                    {"$substr" : [ "$_id.day", 0, 2]}, "-",
                    {"$substr" : [ "$_id.month", 0, 2]}, "-",
                    {"$substr" : [ "$_id.year", 0, 4]}
            ] 
        },
        "Count": 1
    }
}/*,
{ $sort : { "Count : 1 } } 
*/
]);