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
do we have stages of pipeline or pipeline in stages?
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
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
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
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
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
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
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
Querynxm201repeat> db.orders.aggregate([{$limit : 2},{$sort:{price:-1}}])
output
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
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
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
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
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
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
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
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")
}
]
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
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
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
// 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
// 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
// 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 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
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 :
[ { "_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
}
]
}
]
Write an aggregation pipeline to get the total number of orders for each customer.
Write an aggregation pipeline to get the total price of all orders for each customer.
Write an aggregation pipeline to get the average price of all orders for each customer.
Write an aggregation pipeline to get the highest price of all orders for each customer.
Write an aggregation pipeline to get the lowest price of all orders for each customer.
Write an aggregation pipeline to get the total number of orders and the total price of all orders for each customer.
Write an aggregation pipeline to get the total number of orders and the average price of all orders for each customer.
Write an aggregation pipeline to get the highest price and the lowest price of all orders for each customer.
Write an aggregation pipeline to get the total number of orders and the highest price of all orders for each customer.
Write an aggregation pipeline 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 questions, provide the MongoDB Aggregation Pipeline query that solves the problem.
About the author
A full stack developer from India.
Html | Css | Javascript | React | Redux | Tailwind | Node | Express | Mongodb | Sql | Nosql | Socket.io
Comments
Join the discussionNo comments yet. Be the first to comment.