MongoDB Aggregations Examples

1/*
2Lookup with pipelines
3*/
4      .lookup({
5        from: 'cars',
6        as: 'cars',
7        let: { engineerId: '$_id' },
8        pipeline: [
9          {
10            $match: {
11              $expr: {
12                $and: [{ $eq: ['$day', from] }]
13              }
14            }
15          }
16        ]
17      })
18
19
20/*
21Sum all balance (and in this case the amount field type is string)
22Please be aware that monetary data types should be stored as `NumberDecimal` (> Mongo 3.4) *128-bit decimal values
23*/
24{
25    product_name: "transaction",
26    price: { value: NumberDecimal("77.99"), currency: "EUR" }
27}
28
29
30db.transactions.aggregate([
31  {
32    $match: { 
33      "timestamp": { $gte: ISODate("2019-01-01T00:59:00.00+0000") , $lt: ISODate("2019-01-20T23:59:00.00+0000") }      
34    }
35  },
36  {
37    $group: { _id: null, total: {$sum: { "$toDouble": "$amount"  } } }
38  }
39])
40
41// Evaluate operator (before executing)
42db['impressions-200618'].explain('executionStats').aggregate([ $groupOrderOp], { allowDiskUse: true });
43
44// match
45var $groupOrderOp = { $match: { "pathMeta.CampID": {$exists: true}, "pathMeta.CampID": {$ne: "${CampID}"} } }
46db['impressions-200618'].aggregate([ $groupOrderOp], { allowDiskUse: true });
47
48// Find all payments where transactions array is greater than 30 and lookup user email as foreign field from a neighbour collection
49// Project (SELECT)
50db.payments.aggregate([
51  {
52    $lookup:
53    {
54      from: "accounts",
55      localField: "userId",
56      foreignField: "userId",
57      as: "user"
58    }
59  },
60  {
61    $project: {
62      _id: 0, user: 1,
63      sizeOfTransactions: { $size: "$transactions" }
64    }
65  },
66  {
67    $match: { "sizeOfTransactions": { $gte: 30 } }
68  },
69  {
70    $project: { _id: 0, "user.email": 1 }
71  }
72])
73
74
75// Compute total amount per month and return the first record of each group (to record date)
76  const getMonthlyAggregation = (beg, end, amountQuery) => {
77    return FinanceModel.aggregate()
78      .match({ 
79        accountType: 'creditcard',
80        amount: amountQuery,
81        date: { $gte: beg, $lte: end }
82      })
83      .group({
84        _id: "$_id",
85        total: { $sum: "$amount"  },
86        date: { "$first": "$date"},
87
88      })
89      .project({
90        _id: 0,
91        total:1,        
92        date: 1
93      })
94  }
95
96// Filter docs in the collection simple (STAGE OP)
97db.articles.aggregate(
98    [ { $match : { author : "dave" } } ]
99);
100
101// Filter docs with constraints
102db.articles.aggregate(
103    [{ $match: { 
104          $or: [{ 
105            score: { $gt: 90 } 
106          }, { 
107            author: "dave" 
108          }}] 
109    }}]
110);
111
112// Skip and Limit (query only first 2 and return only second one of these 2)
113db.articles.aggregate([
114  { $match: { score: { $gt: 60 }}},
115  { $limit: 2},
116  { $skip: 1 }
117])
118
119// Lookup (JOIN)
120db.orders.aggregate([
121    {
122      $lookup:
123        {
124          from: "inventory",
125          localField: "item",
126          foreignField: "sku",
127          as: "inventory_docs"
128        }
129   }
130])
131// in this example all of the returned docs will have an extra field "inventory_docs":[ {...} ]
132
133
134