# Mongo Db Class 2 Aggregation

Aggregation pipeline has multiple stages

Aggregation Pipeline :  
consists of stages

the input for each stage depends on the output of the previous stage

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1713684529351/efe31dc0-7ef7-4b7e-95ab-e42e16d5d9ae.png align="center")

do we have stages of pipeline or pipeline in stages?

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1713684710363/de387d37-56fe-4ef4-b0fe-865e0012f594.png align="center")

Syntax

db.orders.aggregate(\[{} , {},{},{}\])

inside {} we write query for that stage.

stage 2 will work on output came from stage 1  
stage 3 will work on data came from stage 2.  
stage 4 will work on data came from stage 3 and so on.

so the `` `n`th `` stage works on the output came from \```n`th-1`` stage

every MongoDB aggregator starts with $ symbol

`orders` is the collection name

`nxm201repeat` is the database name

```json
db.orders.insertMany( [   
 { _id: 0, name: "Pepperoni", size: "small", price: 19,quantity: 10, date: ISODate( "2021-03-13T08:14:30Z" ) },    
 { _id: 1, name: "Pepperoni", size: "medium", price: 20,quantity: 20, date : ISODate( "2021-03-13T09:13:24Z" ) },   
 { _id: 2, name: "Pepperoni", size: "large", price: 21,quantity: 30, date : ISODate( "2021-03-17T09:22:12Z" ) },  
 { _id: 3, name: "Cheese", size: "small", price: 12,quantity: 15, date : ISODate( "2021-03-13T11:21:39.736Z" ) },   
 { _id: 4, name: "Cheese", size: "medium", price: 13,quantity:50, date : ISODate( "2022-01-12T21:23:13.331Z" ) },   
 { _id: 5, name: "Cheese", size: "large", price: 14,quantity: 10, date : ISODate( "2022-01-12T05:08:13Z" ) },  
 { _id: 6, name: "Vegan", size: "small", price: 17,quantity: 10, date : ISODate( "2021-01-13T05:08:13Z" ) },
 { _id: 7, name: "Vegan", size: "medium", price: 18,quantity: 10, date : ISODate( "2021-01-13T05:10:13Z" ) } 
] )
```

Query 1.  
`db.orders.aggregate([])`

we will get everything

```json
nxm201repeat> db.orders.aggregate([])
[
  {
    _id: 0,
    name: 'Pepperoni',
    size: 'small',
    price: 19,
    quantity: 10,
    date: ISODate("2021-03-13T08:14:30.000Z")
  },
  {
    _id: 1,
    name: 'Pepperoni',
    size: 'medium',
    price: 20,
    quantity: 20,
    date: ISODate("2021-03-13T09:13:24.000Z")
  },
  {
    _id: 2,
    name: 'Pepperoni',
    size: 'large',
    price: 21,
    quantity: 30,
    date: ISODate("2021-03-17T09:22:12.000Z")
  },
  {
    _id: 3,
    name: 'Cheese',
    size: 'small',
    price: 12,
    quantity: 15,
    date: ISODate("2021-03-13T11:21:39.736Z")
  },
  {
    _id: 4,
    name: 'Cheese',
    size: 'medium',
    price: 13,
    quantity: 50,
    date: ISODate("2022-01-12T21:23:13.331Z")
  },
  {
    _id: 5,
    name: 'Cheese',
    size: 'large',
    price: 14,
    quantity: 10,
    date: ISODate("2022-01-12T05:08:13.000Z")
  },
  {
    _id: 6,
    name: 'Vegan',
    size: 'small',
    price: 17,
    quantity: 10,
    date: ISODate("2021-01-13T05:08:13.000Z")
  },
  {
    _id: 7,
    name: 'Vegan',
    size: 'medium',
    price: 18,
    quantity: 10,
    date: ISODate("2021-01-13T05:10:13.000Z")
  }
]
```

Query 2.

$limit

limit the orders to 2 orders only, currently need only one stage in aggregation pipeline

```json
nxm201repeat> db.orders.aggregate([{$limit : 2}])


[
  {
    _id: 0,
    name: 'Pepperoni',
    size: 'small',
    price: 19,
    quantity: 10,
    date: ISODate("2021-03-13T08:14:30.000Z")
  },
  {
    _id: 1,
    name: 'Pepperoni',
    size: 'medium',
    price: 20,
    quantity: 20,
    date: ISODate("2021-03-13T09:13:24.000Z")
  }
]
```

it works exactly same as how normal limit works

Q3 ) first limit the orders to 3 and then limit to 1 then which Id will we get in result

```json
nxm201repeat> db.orders.aggregate([{$limit : 3} , {$limit : 1}])
[
  {
    _id: 0,
    name: 'Pepperoni',
    size: 'small',
    price: 19,
    quantity: 10,
    date: ISODate("2021-03-13T08:14:30.000Z")
  }
]
```

Q4) $ Sort

sort by the decreasing order of price

```json
nxm201repeat> db.orders.aggregate([{$sort:{price:-1}}])
[
  {
    _id: 2,
    name: 'Pepperoni',
    size: 'large',
    price: 21,
    quantity: 30,
    date: ISODate("2021-03-17T09:22:12.000Z")
  },
  {
    _id: 1,
    name: 'Pepperoni',
    size: 'medium',
    price: 20,
    quantity: 20,
    date: ISODate("2021-03-13T09:13:24.000Z")
  },
  {
    _id: 0,
    name: 'Pepperoni',
    size: 'small',
    price: 19,
    quantity: 10,
    date: ISODate("2021-03-13T08:14:30.000Z")
  },
  {
    _id: 7,
    name: 'Vegan',
    size: 'medium',
    price: 18,
    quantity: 10,
    date: ISODate("2021-01-13T05:10:13.000Z")
  },
  {
    _id: 6,
    name: 'Vegan',
    size: 'small',
    price: 17,
    quantity: 10,
    date: ISODate("2021-01-13T05:08:13.000Z")
  },
  {
    _id: 5,
    name: 'Cheese',
    size: 'large',
    price: 14,
    quantity: 10,
    date: ISODate("2022-01-12T05:08:13.000Z")
  },
  {
    _id: 4,
    name: 'Cheese',
    size: 'medium',
    price: 13,
    quantity: 50,
    date: ISODate("2022-01-12T21:23:13.331Z")
  },
  {
    _id: 3,
    name: 'Cheese',
    size: 'small',
    price: 12,
    quantity: 15,
    date: ISODate("2021-03-13T11:21:39.736Z")
  }
]
```

Q 5) combining $sort and $limit

```json
nxm201repeat> db.orders.aggregate([{$sort : {price:-1}},{$limit :2}])
[
  {
    _id: 2,
    name: 'Pepperoni',
    size: 'large',
    price: 21,
    quantity: 30,
    date: ISODate("2021-03-17T09:22:12.000Z")
  },
  {
    _id: 1,
    name: 'Pepperoni',
    size: 'medium',
    price: 20,
    quantity: 20,
    date: ISODate("2021-03-13T09:13:24.000Z")
  }
]
```

Q 6 ) see this query and tell the output. what will be the ids be in exact order

Query  
`nxm201repeat> db.orders.aggregate([{$limit : 2},{$sort:{price:-1}}])`

output

```json
nxm201repeat> db.orders.aggregate([{$limit : 2},{$sort:{price:-1}}])
[
  {
    _id: 1,
    name: 'Pepperoni',
    size: 'medium',
    price: 20,
    quantity: 20,
    date: ISODate("2021-03-13T09:13:24.000Z")
  },
  {
    _id: 0,
    name: 'Pepperoni',
    size: 'small',
    price: 19,
    quantity: 10,
    date: ISODate("2021-03-13T08:14:30.000Z")
  }
]
```

Q7 ) $match operator

instead of querying like this `db.orders.find({size:"small"})`

we can use $match

write a query using $match to achieve same functionality

```json
nxm201repeat> db.orders.aggregate([{$match : {size:"small"}}])
[
  {
    _id: 0,
    name: 'Pepperoni',
    size: 'small',
    price: 19,
    quantity: 10,
    date: ISODate("2021-03-13T08:14:30.000Z")
  },
  {
    _id: 3,
    name: 'Cheese',
    size: 'small',
    price: 12,
    quantity: 15,
    date: ISODate("2021-03-13T11:21:39.736Z")
  },
  {
    _id: 6,
    name: 'Vegan',
    size: 'small',
    price: 17,
    quantity: 10,
    date: ISODate("2021-01-13T05:08:13.000Z")
  }
]
```

Q 8) write a aggregate query to get medium-sized pizzas

```json
nxm201repeat> db.orders.aggregate([{$match: {size:"medium"}}])
[
  {
    _id: 1,
    name: 'Pepperoni',
    size: 'medium',
    price: 20,
    quantity: 20,
    date: ISODate("2021-03-13T09:13:24.000Z")
  },
  {
    _id: 4,
    name: 'Cheese',
    size: 'medium',
    price: 13,
    quantity: 50,
    date: ISODate("2022-01-12T21:23:13.331Z")
  },
  {
    _id: 7,
    name: 'Vegan',
    size: 'medium',
    price: 18,
    quantity: 10,
    date: ISODate("2021-01-13T05:10:13.000Z")
  }
]
```

Q9. Combining $match with $sort

Find all the large-sized pizzas in increasing order of price

```json
nxm201repeat> db.orders.aggregate([{$match : {size:"large"}},{$sort:{price:1}}])
[
  {
    _id: 5,
    name: 'Cheese',
    size: 'large',
    price: 14,
    quantity: 10,
    date: ISODate("2022-01-12T05:08:13.000Z")
  },
  {
    _id: 2,
    name: 'Pepperoni',
    size: 'large',
    price: 21,
    quantity: 30,
    date: ISODate("2021-03-17T09:22:12.000Z")
  }
]
```

Q 10) Find all small size pizzas with price >= 16

```json
nxm201repeat> db.orders.aggregate([{$match:{size:"small"}},{$match : {price : {$gte : 16}}}])
[
  {
    _id: 0,
    name: 'Pepperoni',
    size: 'small',
    price: 19,
    quantity: 10,
    date: ISODate("2021-03-13T08:14:30.000Z")
  },
  {
    _id: 6,
    name: 'Vegan',
    size: 'small',
    price: 17,
    quantity: 10,
    date: ISODate("2021-01-13T05:08:13.000Z")
  }
]
```

Q 11) do we actually need 2 stages, we can do that in one stage (the above problem)

\[Just because we have hammer everything looks like a nail\]

since we are doing $match only in both the 2 stages, so we can write in one match only i.e in one stage here's how

```json
nxm201repeat> db.orders.aggregate([{$match:{size:"small", price: {$gte: 16}}}])
[
  {
    _id: 0,
    name: 'Pepperoni',
    size: 'small',
    price: 19,
    quantity: 10,
    date: ISODate("2021-03-13T08:14:30.000Z")
  },
  {
    _id: 6,
    name: 'Vegan',
    size: 'small',
    price: 17,
    quantity: 10,
    date: ISODate("2021-01-13T05:08:13.000Z")
  }
]
```

Q 12) Find the smallest pizzas with the highest price

```json
nxm201repeat> db.orders.aggregate([{$match:{size:"small"}},{$sort:{price:-1}},{$limit:1}])
[
  {
    _id: 0,
    name: 'Pepperoni',
    size: 'small',
    price: 19,
    quantity: 10,
    date: ISODate("2021-03-13T08:14:30.000Z")
  }
]
```

Q 13) Find the smallest pizzas with the lowest price

```json
nxm201repeat> db.orders.aggregate([{$match:{size:"small"}},{$sort:{price:1}},{$limit:1}])
[
  {
    _id: 3,
    name: 'Cheese',
    size: 'small',
    price: 12,
    quantity: 15,
    date: ISODate("2021-03-13T11:21:39.736Z")
  }
]
```

[`$group`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/group/)

you have to group by group by key

if we want to make a group, then there should be something to group by

It's used to group by "something", like "course" ,"batch","course\_time" etc

Q 14) group the pizzas according to size

```bash
nxm201repeat> db.orders.aggregate([{$group:{_id:"$size"}}])
[ { _id: 'small' }, { _id: 'medium' }, { _id: 'large' } ]
```

inside group we have written "$size" because size is not a simple value its a key

whatever we are grouping we are putting into `_id`

Q 15) group by name

```bash
nxm201repeat> db.orders.aggregate([{$group:{_id:"$name"}}])
[ { _id: 'Pepperoni' }, { _id: 'Vegan' }, { _id: 'Cheese' } ]
```

here this group is having information of all the sub groups its just that it is not visible now here. You can perform query on that.

Q16 ) Find the total number of samll size pizzas it should be like 35

```bash
// query
nxm201repeat>

db.orders.aggregate([{$group:{_id:"$size",totalQuantity:{$sum : "$quantity"}}}])


//output
[
  { _id: 'medium', totalQuantity: 80 },
  { _id: 'large', totalQuantity: 40 },
  { _id: 'small', totalQuantity: 35 }
]
```

Q17 ) Now Lets suppose I want data of only and only small size pizzas what modification in the above query

Q 18) Find the total number of Vegan pizzas

```bash
// query
nxm201repeat> 
db.orders.aggregate([{$group:{_id: "$name",total_qty : {$sum : "$quantity"}}},{$match:{_id:"Vegan"}}])

// output
[ { _id: 'Vegan', total_qty: 20 } ]
```

Insert this data into countries collection

Q19) Find the state with largest population in the given countries data

```bash
// query
nxm201repeat> 

db.countries.aggregate([{$group:{_id:"$state", totalPopulation : {$sum : "$pop"}}},{$sort:{totalPopulation : -1}},{$limit:1}])

// response
[ { _id: 'CA', totalPopulation: 29754890 } ]
```

Assignments

Assignment 1  
Go to [**https://media.mongodb.org/zips.json**](https://media.mongodb.org/zips.json) and download the large dataset of zips

Write aggregation queries for following:

* find largest population city in every state
    
* find average population per state by cities
    
* find the lowest population city
    

Q1 find largest population city in every state

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1714235083142/e571763f-8a8d-42c0-a51d-dc078c78d18f.png align="center")

  

```bash
nxm201repeat> db.countries.aggregate([{$group: { _id: "$state",largestCity:{ $max: { city: "$city", population: "$pop" } }}},
{ $project: {state: "$_id", largestCity: "$largestCity.city",population: "$largestCity.population",_id: 0 } }])


[
  { state: 'FL', largestCity: 'ZOLFO SPRINGS', population: 3515 },
  { state: 'AZ', largestCity: 'YUMA PROVING GRO', population: 28179 },
  { state: 'NV', largestCity: 'YERINGTON', population: 6157 },
  { state: 'NY', largestCity: 'YULAN', population: 107 },
  { state: 'CA', largestCity: 'ZAMORA', population: 317 },
  { state: 'KY', largestCity: 'ZOE', population: 190 },
  { state: 'HI', largestCity: 'WAIPAHU', population: 52411 },
  { state: 'NE', largestCity: 'YUTAN', population: 1838 },
  { state: 'TX', largestCity: 'ZEPHYR', population: 433 },
  { state: 'OR', largestCity: 'ZIGZAG', population: 1325 },
  { state: 'PA', largestCity: 'ZIONSVILLE', population: 2312 },
  { state: 'DE', largestCity: 'WILMINGTON', population: 40087 },
  { state: 'MS', largestCity: 'YAZOO CITY', population: 17936 },
  { state: 'VT', largestCity: 'WORCESTER', population: 906 },
  { state: 'OH', largestCity: 'ZOARVILLE', population: 2969 },
  { state: 'NH', largestCity: 'WOODSVILLE', population: 2292 },
  { state: 'ME', largestCity: 'YORK', population: 8477 },
  { state: 'IA', largestCity: 'ZWINGLE', population: 1320 },
  { state: 'MA', largestCity: 'YARMOUTH PORT', population: 5735 },
  { state: 'NJ', largestCity: 'YARDVILLE', population: 12373 }
]
```

  
  
Q2 ) find average population per state by cities

Assignment 2  
  
**Instructions**

dataset :

```bash
[  {    "_id": 1,    "name": "John",    "age": 30,    "address": {      "city": "New York",      "state": "NY",      "zip": "10001"    },    "orders": [      {        "id": 101,        "product": "Phone",        "price": 1000,        "quantity": 1      },      {        "id": 102,        "product": "Laptop",        "price": 2000,        "quantity": 1      }    ]
  },
  {
    "_id": 2,
    "name": "Jane",
    "age": 25,
    "address": {
      "city": "San Francisco",
      "state": "CA",
      "zip": "94107"
    },
    "orders": [
      {
        "id": 103,
        "product": "Tablet",
        "price": 500,
        "quantity": 2
      },
      {
        "id": 104,
        "product": "Smartwatch",
        "price": 300,
        "quantity": 1
      }
    ]
  },
  {
    "_id": 3,
    "name": "Bob",
    "age": 40,
    "address": {
      "city": "Chicago",
      "state": "IL",
      "zip": "60601"
    },
    "orders": [
      {
        "id": 105,
        "product": "Camera",
        "price": 800,
        "quantity": 1
      },
      {
        "id": 106,
        "product": "Headphones",
        "price": 100,
        "quantity": 2
      }
    ]
  }
]
```

1. Write an [agg](https://pastebin.com/0sQyxddD)regation pipe[line](https://pastebin.com/0sQyxddD) to get the total number of orders for each customer.
    
2. Write an aggregation pipe[line](https://pastebin.com/0sQyxddD) to get the total price of all orders for each customer.
    
3. Write an aggregation pipe[line](https://pastebin.com/0sQyxddD) to get the average price of all orders for each customer.
    
4. Write an aggregation pipe[line](https://pastebin.com/0sQyxddD) to get the highest price of all orders for each customer.
    
5. Write an aggregation pipe[line](https://pastebin.com/0sQyxddD) to get the lowest price of all orders for each customer.
    
6. Write an aggregation pipe[line](https://pastebin.com/0sQyxddD) to get the total number of orders and the total price of all orders for each customer.
    
7. Write an aggregation pipe[line](https://pastebin.com/0sQyxddD) to get the total number of orders and the average price of all orders for each customer.
    
8. Write an aggregation pipe[line](https://pastebin.com/0sQyxddD) to get the highest price and the lowest price of all orders for each customer.
    
9. Write an aggregation pipe[line](https://pastebin.com/0sQyxddD) to get the total number of orders and the highest price of all orders for each customer.
    
10. Write an aggregation pipe[line](https://pastebin.com/0sQyxddD) to get the total number of orders, the total price, and the average price of all orders for each customer.
    

For each of the above que[stio](https://pastebin.com/0sQyxddD)ns, provide the MongoDB Aggregation Pipeline query that solves the problem.