NDC Oslo MongoDB Aggregation Queries

mongodb-aggregation-samples.js

//simple group
db.workitems.aggregate(
{
    $group: { _id: "$Owner", "Count": { $sum : 1 } } }
); 


//group with composite key
db.workitems.aggregate(
{
    $group: { _id: { Owner: "$Owner", Effort: "$Effort" }, "Count": { $sum : 1 } } }
); 


//sorted group by
db.workitems.aggregate(
{
    $group: { _id: "$Owner", "Count": { $sum : 1 } } }, 
    { $sort : { Count : -1 } } 
); 

//group by with query
db.workitems.aggregate(
    [
        { $match: { Owner : "Molly" } },
        { $group: { _id: "$Owner", "Count": { $sum : 1 } } }
    ]
 ); 

//simple projection
db.workitems.aggregate(
    { $project: { "Tasks.Assignee": 1 }}
); 

//simple unwind
db.workitems.aggregate(
    { $unwind: "$Tasks" }
); 


//projecting and unwinding
db.workitems.aggregate(
    { $project: { Description: 1, TimeRemaining: "$Tasks.TimeRemaining", _id: 0 }},
    { $unwind: "$TimeRemaining" }
); 


//project, unwind, group
db.workitems.aggregate(
    { $project: { Description: 1, TimeRemaining: "$Tasks.TimeRemaining", _id: 0 }},
    { $unwind: "$TimeRemaining" },
    { $group: { _id: "$Description", Total: { "$sum" :  "$TimeRemaining" } } }
); 


//output results to a collection
db.workitems.aggregate(
    { $unwind: "$Tasks" },
    { $project: { Assignee: "$Tasks.Assignee", TimeRemaining: "$Tasks.TimeRemaining", _id: 0 } },
    { $group: { _id: "$Assignee", Total: { "$sum" : "$TimeRemaining" } } },
    { $out: "TotalTimeRemaining" }
); 
    
db.TotalTimeRemaining.find();

//push, and group
db.workitems.aggregate(
    { $unwind: "$Tasks" },
    { $group: { _id: "$Tasks.Assignee", Tasks: { $push: "$Tasks.Description" } } },
    { $out: "AssigneeTasks" }
); 
    
db.AssigneeTasks.find();
 
//projecting, unwinding, and grouping
db.workitems.aggregate(
    { $unwind: "$Tasks" },
    { $project: { Assignee: "$Tasks.Assignee", TimeRemaining: "$Tasks.TimeRemaining", _id: 0 } },
    { $group: { _id: "$Assignee", Total: { "$sum" : "$TimeRemaining" } } }
); 

//variables and conditions
db.workitems.aggregate(
    { $unwind: "$Tasks" },
    { $project: { 
                    Assignee: "$Tasks.Assignee", 
                    TimeRemaining: "$Tasks.TimeRemaining", 
                    Effort: 1, _id: 0,
                    PredictedEffort: {
                        $let: { 
                            vars: {
                                multiplier: { $cond: { if: { $gte: ["$Effort", 13] }, then: 1.5, else: 1 } }     
                            },
                            in: { $multiply: ["$$multiplier", "$Effort"] }
    } } } } ); 

//average
db.workitems.aggregate(
    { $unwind: "$Tasks" },
    { $project: { Assignee: "$Tasks.Assignee", TimeRemaining: "$Tasks.TimeRemaining", _id: 0 } },
    { $group: { _id: "$Assignee", Average: { "$avg" : "$TimeRemaining" } } }
); 

//min, and max
db.workitems.aggregate(
    { $unwind: "$Tasks" },
    { $project: { Assignee: "$Tasks.Assignee", TimeRemaining: "$Tasks.TimeRemaining", _id: 0 } },
    { $group: { _id: "$Assignee", Min: { "$min" : "$TimeRemaining" } } }
); 

db.workitems.aggregate(
    { $unwind: "$Tasks" },
    { $project: { Assignee: "$Tasks.Assignee", TimeRemaining: "$Tasks.TimeRemaining", _id: 0 } },
    { $group: { _id: "$Assignee", Max: { "$max" : "$TimeRemaining" } } }
); 


//first, and last
db.workitems.aggregate(
    { $unwind: "$Tasks" },
    { $project: { Assignee: "$Tasks.Assignee", TimeRemaining: "$Tasks.TimeRemaining", _id: 0 } },
    { $sort: { TimeRemaining: -1 } },
    { $group: { _id: "$Assignee", Last: { "$last" : "$TimeRemaining" } } }
); 

db.workitems.aggregate(
    { $unwind: "$Tasks" },
    { $project: { Assignee: "$Tasks.Assignee", TimeRemaining: "$Tasks.TimeRemaining", _id: 0 } },
    { $sort: { TimeRemaining: 1 } },
    { $group: { _id: "$Assignee", First: { "$first" : "$TimeRemaining" } } }
); 


//having average greater than
db.workitems.aggregate(
    { $unwind: "$Tasks" },
    { $project: { Assignee: "$Tasks.Assignee", TimeRemaining: "$Tasks.TimeRemaining", _id: 0 } },
    { $group: { _id: "$Assignee", Average: { "$avg" : "$TimeRemaining" } } },
    { $match: { Average: { $gt: 14 } } } 
); 


//redacting
var user = "John";
db.workitems.aggregate(
    { $unwind: "$Tasks" },
    { $project: { Owner: 1, Assignee: "$Tasks.Assignee", TimeRemaining: "$Tasks.TimeRemaining", _id: 0 } },
    { $redact: 
        { $cond: {
            if: { $eq: ["$Owner", user] },
            then: "$$DESCEND",
            else: "$$PRUNE"           
        } } }  
);