aggregate

Method definition

aggregate(pipeline: object[], options?: object): Promise<MongoResult>

Request parameters

The options structure is defined as follows:

Example

This example shows how to use the aggregate pipeline on the orders table, which contains pizza order data.

Generate demo data

Open the ApsaraDB console. Create the orders table, select it, and then select Advanced Mode. Insert the following demo data:

db.insertMany( [
   { name: "Pepperoni", size: "small", price: 19,
     quantity: 10, date : "2021-03-13T08:14:30.000Z" },
   { name: "Pepperoni", size: "medium", price: 20,
     quantity: 20, date : "2021-03-13T09:13:24.000Z" },
   { name: "Pepperoni", size: "large", price: 21,
     quantity: 30, date : "2021-03-17T09:22:12.000Z" },
   { name: "Cheese", size: "small", price: 12,
     quantity: 15, date : "2021-03-13T11:21:39.000Z" },
   { name: "Cheese", size: "medium", price: 13,
     quantity: 50, date : "2022-01-12T21:23:13.000Z" },
   { name: "Cheese", size: "large", price: 14,
     quantity: 10, date : "2022-01-12T05:08:13.000Z" },
   { name: "Vegan", size: "small", price: 17,
     quantity: 10, date : "2021-01-13T05:08:13.000Z" },
   { name: "Vegan", size: "medium", price: 18,
     quantity: 10, date : "2021-01-13T05:10:13.000Z" }
] )

Calculate the total number of orders

This code finds orders in the orders collection where the size is medium. It then groups the results by the `name` field and calculates the sum of the quantity field.

db.aggregate( [

    // Stage 1: Filter by pizza size
    {
        $match: { size: "medium" }
    },

    // Stage 2: Group the matching orders by name and sum the quantity
    {
        $group: { _id: "$name", totalQuantity: { $sum: "$quantity" } }
    }

] )

Output:

[
   { _id: 'Cheese', totalQuantity: 50 },
   { _id: 'Vegan', totalQuantity: 10 },
   { _id: 'Pepperoni', totalQuantity: 20 }
]

Calculate the order amount and average order count by date

db.aggregate( [

   // Stage 1: Filter for orders with dates between 2020-01-30 and 2022-01-30
   {
      $match:
      {
         "date": { $gte: "2020-01-30T00:00:00.000Z", $lt: "2022-01-30T00:00:00.000Z" }
      }
   },

   // Stage 2: Group by date and calculate the order amount and average order quantity
   {
      $group:
      {
         _id: { $dateToString: { format: "%Y-%m-%d", date: "$date" } },
         totalOrderValue: { $sum: { $multiply: [ "$price", "$quantity" ] } },
         averageOrderQuantity: { $avg: "$quantity" }
      }
   },
  
   // Stage 3: Sort by order amount in descending order
   {
      $sort: { totalOrderValue: -1 }
   }

] )

Output:

[
   { _id: '2022-01-12', totalOrderValue: 790, averageOrderQuantity: 30 },
   { _id: '2021-03-13', totalOrderValue: 770, averageOrderQuantity: 15 },
   { _id: '2021-03-17', totalOrderValue: 630, averageOrderQuantity: 30 },
   { _id: '2021-01-13', totalOrderValue: 350, averageOrderQuantity: 10 }
]