Aggregation

• Aggregation is the process of performing tansformation on documents and combining them to produce computed results.
• Pipeline Stages: Aggregations consists of multiple pipeline stages, each performing a specific operation on the input data.
• Benefits: Complex Calculations and operations are possible. Data can be combined, reshaped and computed for insights. Aggregations handles large datasets efficiently.

$match

• The $match stage is similar to the query find. It filters documents based on specified conditions.

• Find all the products whose name is "Sleek Wooden Tuna".

shop> db.products.aggregate([{$match: {'name': 'Sleek Wooden Tuna'}}])

• Find products whose price is greater than 1250.

shop> db.products.aggregate([{$match: {price: {$gt: 1250}}}])

$group

• Find total products by company.

shop> db.products.aggregate([{$group: {_id: "$company", totalProducts: {$sum: 1}}}])
[
  { _id: '64c23350e32f4a51b19b9257', totalProducts: 1 },
  { _id: ObjectId("64c23350e32f4a51b19b924a"), totalProducts: 387 },
  { _id: ObjectId("64c23350e32f4a51b19b924d"), totalProducts: 342 },
  { _id: '64c23350e32f4a51b19b924d', totalProducts: 2 },
  { _id: ObjectId("64c23350e32f4a51b19b9241"), totalProducts: 344 },
  { _id: ObjectId("64c23350e32f4a51b19b9248"), totalProducts: 374 },
  { _id: '64c23350e32f4a51b19b923e', totalProducts: 7 },
  { _id: '64c23350e32f4a51b19b9237', totalProducts: 6 },
  { _id: '64c23350e32f4a51b19b9244', totalProducts: 2 },
  { _id: '64c23350e32f4a51b19b9239', totalProducts: 6 },
  { _id: '64c23350e32f4a51b19b9242', totalProducts: 6 },
  { _id: '64c23350e32f4a51b19b9253', totalProducts: 1 },
  { _id: ObjectId("64c23350e32f4a51b19b9245"), totalProducts: 313 },
  { _id: ObjectId("64c23350e32f4a51b19b923d"), totalProducts: 337 },
  { _id: ObjectId("64c23350e32f4a51b19b924c"), totalProducts: 335 },
  { _id: ObjectId("64c23350e32f4a51b19b923c"), totalProducts: 356 },
  { _id: ObjectId("64c23350e32f4a51b19b9238"), totalProducts: 347 },
  { _id: '64c23350e32f4a51b19b923f', totalProducts: 5 },
  { _id: '64c23350e32f4a51b19b924b', totalProducts: 2 },
  { _id: '64c23350e32f4a51b19b9255', totalProducts: 1 }
]

• Find sum of price of products by company.

shop> db.products.aggregate([{$group: {_id: "$company", totalPrice: {$sum: '$price'}}}])
[
  { _id: '64c23350e32f4a51b19b9257', totalPrice: 79 },
  { _id: ObjectId("64c23350e32f4a51b19b924a"), totalPrice: 187995 },
  { _id: ObjectId("64c23350e32f4a51b19b924d"), totalPrice: 166591 },
  { _id: '64c23350e32f4a51b19b924d', totalPrice: 218 },
  { _id: ObjectId("64c23350e32f4a51b19b9241"), totalPrice: 168643 },
  { _id: ObjectId("64c23350e32f4a51b19b9248"), totalPrice: 189240 },
  { _id: '64c23350e32f4a51b19b923e', totalPrice: 303 },
  { _id: '64c23350e32f4a51b19b9237', totalPrice: 784 },
  { _id: '64c23350e32f4a51b19b9244', totalPrice: 168 },
  { _id: '64c23350e32f4a51b19b9239', totalPrice: 1704 },
  { _id: '64c23350e32f4a51b19b9242', totalPrice: 704 },
  { _id: '64c23350e32f4a51b19b9253', totalPrice: 69 },
  { _id: ObjectId("64c23350e32f4a51b19b9245"), totalPrice: 153241 },
  { _id: ObjectId("64c23350e32f4a51b19b923d"), totalPrice: 165639 },
  { _id: ObjectId("64c23350e32f4a51b19b924c"), totalPrice: 169452 },
  { _id: ObjectId("64c23350e32f4a51b19b923c"), totalPrice: 177608 },
  { _id: ObjectId("64c23350e32f4a51b19b9238"), totalPrice: 173258 },
  { _id: '64c23350e32f4a51b19b923f', totalPrice: 2015 },
  { _id: '64c23350e32f4a51b19b924b', totalPrice: 328 },
  { _id: '64c23350e32f4a51b19b9255', totalPrice: 299 }
]

• Find sum of price of products whose price is greater than 900

db.products.aggregate([
  {$match : {price: {$gt: 900}}},
  {$group: {_id: "$company", totalPrice: {$sum: '$price'}}}
])

• Find sales whose quantity is 5 and then find the sum and average price.

shop> db.sales.aggregate([
  {$match: {quantity: 5}},
  {$group: {_id: '$quantity', priceTotal: {$sum: '$price'}, priceAvg: {$avg: '$price'}}}
])
[ { _id: 5, priceTotal: 135, priceAvg: 45 } ]

• Sort the last query in descending order.

shop> db.products.aggregate([
  {$match : {price: {$gt: 1200}}},
  {$group: {_id: "$company", totalPrice: {$sum: '$price'}}},
  {$sort: {totalPrice: -1}}
])

$project

• The $project stage reshapes documents, includes or excludes fields, and performs operations on fields.

• It is similar to projection.

shop> db.products.aggregate([{$project: {price: 1, _id:0}}])
[
  { price: 89 },   { price: 699 },
  { price: 1299 }, { price: 149 },
  { price: 59 },   { price: 89 },
  { price: 799 },  { price: 349 },
  { price: 19 },   { price: 69 },
  { price: 129 },  { price: 29 },
  { price: 899 },  { price: 39 },
  { price: 249 },  { price: 49 },
  { price: 299 },  { price: 599 },
  { price: 45 },   { price: 79 }
]

• Calculate the discount price from the price. We are giving 0.8 percent discount.

shop> db.products.aggregate([{$project: {price: 1, discountPrice: {$multiply: ['$price', 0.8]}}}])
[
  {
    _id: ObjectId("64c23601e32f4a51b19b9265"),
    price: 89,
    discountPrice: 71.2
  },
]

• Calculate the last results whose price is greater than 5.

shop> db.products.aggregate([
  {$match: {price: {$gt: 5}}},
  {$project: {price: 1, discountPrice: {$multiply: ['$price', 0.8]}}}
])

$push, $unwind, $addToSet

• $push creates an array of elements
• $unwind wraps multiple nested array of elements into single array of elements
• $addToSet removes duplicates from the array.

shop> db.products.aggregate([
  {$match: {price: {$gt: 1200}}},
  {$group: {_id: '$price', allColors: {$push:'$colors'}}}
])
[
  {
    _id: 1999,
    allColors: [
      [ '#000000', '#cc6600', '#663300' ],
      [ '#000000', '#cc6600', '#663300' ]
    ]
  },
  { _id: 1299, allColors: [ [ '#333333', '#cccccc', '#00ff00' ] ] }
]

shop> db.products.aggregate([
  {$match: {price: {$gt: 1200}}},
  {$unwind: '$colors'},
  {$group: {_id: '$price', allColors: {$push:'$colors'}}}
])
[
  {
    _id: 1999,
    allColors: [
      '#000000',
      '#cc6600',
      '#663300',
      '#000000',
      '#cc6600',
      '#663300'
    ]
  },
  { _id: 1299, allColors: [ '#333333', '#cccccc', '#00ff00' ] }
]

shop> db.products.aggregate([
  {$match: {price: {$gt: 1200}}},
  {$unwind: '$colors'},
  {$group: {_id: '$price', allColors: {$addToSet:'$colors'}}}
])
[
  { _id: 1299, allColors: [ '#00ff00', '#cccccc', '#333333' ] },
  { _id: 1999, allColors: [ '#000000', '#cc6600', '#663300' ] }
]

filters

shop> db.col.aggregate([{$project: {name: 1, prabinValue: {$filter: {input: "$values",as: 'val',cond: {$gt: ['$$val', 30]}}}}}])
[
  {
    _id: { _id: '64c23350e32f4a51b19b9201' },
    name: 'Document 1',
    prabinValue: [ 40, 50 ]
  },
  {
    _id: { _id: '64c23350e32f4a51b19b9202' },
    name: 'Document 2',
    prabinValue: [ 35, 45, 55 ]
  },
  {
    _id: { _id: '64c23350e32f4a51b19b9203' },
    name: 'Document 3',
    prabinValue: [ 35, 45 ]
  },
  {
    _id: { _id: '64c23350e32f4a51b19b9204' },
    name: 'Document 4',
    prabinValue: [ 40, 50, 60, 70 ]
  },
  {
    _id: { _id: '64c23350e32f4a51b19b9205' },
    name: 'Document 5',
    prabinValue: [ 35, 45, 55, 65 ]
  }
]