mongoose - group collection data by multiple fields mongodb -
collection structure
order = new schema index: { type: number, unique: true } number: date status: { type: string, enum: ['success', 'failure'] } created_at: { type: date, default: date.now } updated_at: { type: date, default: date.now } i need query returns me array of objects having data success count , failure count grouped date. ex-
orders = { 28-10-2016:{ success_count: 10, failure_count: 10 }, 29-10-2016: { success_count: 10, failure_count: 10 } }
with aggregation framework, result different "desired" output instead of having hash keys, array of objects _id key having value represents group field. instance, instead of
{ "28-10-2016":{ "success_count": 10, "failure_count": 10 }, "29-10-2016": { "success_count": 10, "failure_count": 10 } } you'd have better structure like
[ { "_id": "28-10-2016", "success_count": 10, "failure_count": 10 }, "_id": "29-10-2016", "success_count": 10, "failure_count": 10 } ] accomplishing above result require using $cond operator in $sum accumulator operator. $cond operator evaluate logical condition based on first argument (if) , returns second argument evaluation true (then) or third argument false (else). converts true/false logic 1 , 0 numerical values feed $sum respectively:
"success_count": { "$sum": { "$cond": [ { "$eq": [ "$status", "success" ] }, 1, 0 ] } } as resulting pipeline, 1 needs run aggregation operation uses $datetostring operator in _id key expression $group pipeline:
orders.aggregate([ { "$group": { "_id": { "$datetostring": { "format": "%y-%m-%d", "date": "$created_at" } }, "success_count": { "$sum": { "$cond": [ { "$eq": [ "$status", "success" ] }, 1, 0 ] } }, "failure_count": { "$sum": { "$cond": [ { "$eq": [ "$status", "failure" ] }, 1, 0 ] } } } } ], function (err, orders){ if (err) throw err; console.log(orders); }) however, there more flexible , better performant approach executes faster above, efficient data structure aggregation result follows schema example:
orders = [ { "_id": "28-10-2016", "counts": [ { "status": "success", "count": 10 }, { "status": "failure", "count": 10 } ] }, { "_id": "29-10-2016", "counts": [ { "status": "success", "count": 10 }, { "status": "failure", "count": 10 } ] } ] then consider running alternative pipeline follows
orders.aggregate([ { "$group": { "_id": { "date": { "$datetostring": { "format": "%y-%m-%d", "date": "$created_at" } }, "status": { "$tolower": "$status" } }, "count": { "$sum": 1 } } }, { "$group": { "_id": "$_id.date", "counts": { "$push": { "status": "$_id.status", "count": "$count" } } } } ], function (err, orders){ if (err) throw err; console.log(orders); })
Comments
Post a Comment