MongoDB Aggregation – Pipeline

1. Example of $group
>db.mybookstore.aggregate({$group:{_id:"$author",total_availabilty:{$s um:"$available"},total_bookings:{$sum:"$bookings"}}})
{ "_id" : "kitten lens", "total_availabilty" : 159, "total_bookings" : 175 }
{ "_id" : "lay shore", "total_availabilty" : 99, "total_bookings" : 44
 }
{ "_id" : "dev shinde", "total_availabilty" : 92, "total_bookings" :
 71 }
{ "_id" : "george haligs", "total_availabilty" : 25, "total_bookings"
 : 7 }
{ "_id" : "steve markison", "total_availabilty" : 32, "total_bookings"
 : 10 }

2. Example of $project and Airthmetic Operators
>db.mybookstore.aggregate({$group:{_id:"$author",total_availability:{$ sum:"$available"},total_bookings:{$sum:"$bookings"}}},{$project:{_id:0 ,author:"$_id",availability:{$subtract:["$total_availability","$total_ bookings"]}}})
{ "author" : "kitten lens", "availability" : -16 }
{ "author" : "lay shore", "availability" : 55 }
{ "author" : "dev shinde", "availability" : 21 }
{ "author" : "george haligs", "availability" : 18 }
{ "author" : "steve markison", "availability" : 22 }

3. Example of $match and Equality Operators
>db.mybookstore.aggregate({$group:{_id:"$author",total_availability:{$ sum:"$available"},total_bookings:{$sum:"$bookings"}}},{$project:{_id:0 ,author:"$_id",availability:{$subtract:["$total_availability","$total_ bookings"]}}},{$match:{availability:{$gte:20}}})
{ "author" : "lay shore", "availability" : 55 }
{ "author" : "dev shinde", "availability" : 21 }
{ "author" : "steve markison", "availability" : 22 }

4. Example of $addToSet
>db.mybookstore.aggregate({$group:{_id:"$store",books:{$addToSet:"$tit le"}}}).pretty()
{
"_id" : "royal book stores",
"books" : [
"day in",
"day out 3",
"day out",
"day out 2",
"Home Alone 3",
"Home Alone 2",
"Home Alone"
]
}
{ "_id" : "sameer bandhu", "books" : [ "Haveli" ] }
{ "_id" : "raman book gallery", "books" : [ "Haunted House" ] }
{ "_id" : "cine book office", "books" : [ "spy kids 2", "spy kids" ] }
{ "_id" : "laxman book depot", "books" : [ "Humpty Dumpty" ] }

5. Example of $push
>db.mybookstore.aggregate({$group:{_id:"$store",books:{$push:"$title"} }}).pretty()
{
"_id" : "royal book stores",
"books" : [
"Home Alone",
"Home Alone 2",
"Home Alone 3",
"day out",
"day out 2",
"day out 3",
"day in"
]
}
{ "_id" : "sameer bandhu", "books" : [ "Haveli" ] }
{ "_id" : "raman book gallery", "books" : [ "Haunted House" ] }
{ "_id" : "cine book office", "books" : [ "spy kids", "spy kids 2" ] }
{ "_id" : "laxman book depot", "books" : [ "Humpty Dumpty" ] }

6. Example of $sort
>db.mybookstore.aggregate({$group:{_id:{seller:"$store",book:"$title"} ,availability:{$sum:"$available"}}},{$sort:{availability:1}})
{ "_id" : { "seller" : "cine book office", "book" : "spy kids 2" },
"availability" : 3 }
{ "_id" : { "seller" : "royal book stores", "book" : "Home Alone 3" },
"availability" : 5 }
{ "_id" : { "seller" : "royal book stores", "book" : "day in" },
"availability" : 9 }
{ "_id" : { "seller" : "royal book stores", "book" : "Home Alone 2" },
"availability" : 12 }
{ "_id" : { "seller" : "cine book office", "book" : "spy kids" },
"availability" : 14 }
{ "_id" : { "seller" : "royal book stores", "book" : "day out 3" },
"availability" : 22 }
{ "_id" : { "seller" : "raman book gallery", "book" : "Haunted House"
}, "availability" : 22 }
{ "_id" : { "seller" : "laxman book depot", "book" : "Humpty Dumpty"
}, "availability" : 32 }
{ "_id" : { "seller" : "royal book stores", "book" : "day out" },
"availability" : 36 }
{ "_id" : { "seller" : "sameer bandhu", "book" : "Haveli" },
"availability" : 78 }
{ "_id" : { "seller" : "royal book stores", "book" : "day out 2" },
"availability" : 87 }
{ "_id" : { "seller" : "royal book stores", "book" : "Home Alone" },
"availability" : 87 }

7. Example of $first
>db.mybookstore.aggregate({$group:{_id:{seller:"$store",book:"$title"} ,availability:{$sum:"$available"}}},{$sort:{availability:1}},{$group:{ _id:"$_id.seller",least_available:{$first:"$_id.book"}}})
{ "_id" : "sameer bandhu", "least_available" : "Haveli" }
{ "_id" : "raman book gallery", "least_available" : "Haunted House" }
{ "_id" : "royal book stores", "least_available" : "Home Alone 3" }
{ "_id" : "laxman book depot", "least_available" : "Humpty Dumpty" }
{ "_id" : "cine book office", "least_available" : "spy kids 2" }

8. Example of $last
>db.mybookstore.aggregate({$group:{_id:{seller:"$store",book:"$title"} ,availability:{$sum:"$available"}}},{$sort:{availability:1}},{$group:{ _id:"$_id.seller",most_available:{$last:"$_id.book"}}})
{ "_id" : "sameer bandhu", "most_available" : "Haveli" }
{ "_id" : "raman book gallery", "most_available" : "Haunted House" }
{ "_id" : "royal book stores", "most_available" : "Home Alone" }
{ "_id" : "laxman book depot", "most_available" : "Humpty Dumpty" }
{ "_id" : "cine book office", "most_available" : "spy kids" }

9. Example of $max
>db.mybookstore.aggregate({$group:{_id:{seller:"$store",book:"$title"} ,availability:{$sum:"$available"}}},{$sort:{availability:1}},{$group:{ _id:"$_id.seller",max_availability:{$max:"$availability"}}})
{ "_id" : "sameer bandhu", "max_availability" : 78 }
{ "_id" : "raman book gallery", "max_availability" : 22 }
{ "_id" : "royal book stores", "max_availability" : 87 }
{ "_id" : "laxman book depot", "max_availability" : 32 }
{ "_id" : "cine book office", "max_availability" : 14 }

10.	Example of $min
>db.mybookstore.aggregate({$group:{_id:{seller:"$store",book:"$title"} ,availability:{$sum:"$available"}}},{$sort:{availability:1}},{$group:{ _id:"$_id.seller",min_availability:{$min:"$availability"}}})
{ "_id" : "sameer bandhu", "min_availability" : 78 }
{ "_id" : "raman book gallery", "min_availability" : 22 }
{ "_id" : "royal book stores", "min_availability" : 5 }
{ "_id" : "laxman book depot", "min_availability" : 32 }
{ "_id" : "cine book office", "min_availability" : 3 }

11.	Example of $sum
>db.mybookstore.aggregate({$group:{_id:{seller:"$store"},stock:{$sum:" $available"}}})
{ "_id" : { "seller" : "royal book stores" }, "stock" : 258 }
{ "_id" : { "seller" : "sameer bandhu" }, "stock" : 78 }
{ "_id" : { "seller" : "raman book gallery" }, "stock" : 22 }
{ "_id" : { "seller" : "cine book office" }, "stock" : 17 }
{ "_id" : { "seller" : "laxman book depot" }, "stock" : 32 }

12.	Example of String Operators
>db.mybookstore.aggregate({$group:{_id:"$store"}},{$project:{_id:{$toU pper:"$_id"}}}).pretty()
{ "_id" : "ROYAL BOOK STORES" }
{ "_id" : "SAMEER BANDHU" }
{ "_id" : "RAMAN BOOK GALLERY" }
{ "_id" : "CINE BOOK OFFICE" }
{ "_id" : "LAXMAN BOOK DEPOT" }

13.	Example of $limit
>db.mybookstore.aggregate({$group:{_id:"$store"}},{$limit:2}).pretty()
{ "_id" : "royal book stores" }
{ "_id" : "sameer bandhu" }

14.	Example of $skip
>	db.mybookstore.aggregate({$group:{_id:"$store"}},{$skip:2}).pretty()
{ "_id" : "raman book gallery" }
{ "_id" : "cine book office" }
{ "_id" : "laxman book depot" }

15.	Example of $unwind
>db.mybookstore.aggregate({$project:{title:1,category:1,author:1}},{$u nwind:"$category"},{$limit:5}).pretty()
{
"_id" : 1,
"title" : "Humpty Dumpty",
"category" : "comic",
"author" : "steve markison"
}
{
"_id" : 1,
"title" : "Humpty Dumpty",
"category" : "kids",
"author" : "steve markison"
}
{
"_id" : 1,
"title" : "Humpty Dumpty",
"category" : "laughter",
"author" : "steve markison"
}
{
"_id" : 2,
"title" : "Haunted House",
"category" : "thriller",
"author" : "george haligs"
}
{
"_id" : 2,
"title" : "Haunted House",
"category" : "horror",
"author" : "george haligs"
}

16.	Example of $out
>db.mybookstore.aggregate({$project:{_id:0,title:1,category:1,author:1 }},{$unwind:"$category"},{$limit:5},{$out:"cats"}).pretty()
> db.cats.find().pretty()
{
"_id" : ObjectId("53bf49b68ac4e138b9989677"),
"title" : "Humpty Dumpty",
"category" : "comic",
"author" : "steve markison"
}
{
"_id" : ObjectId("53bf49b68ac4e138b9989678"),
"title" : "Humpty Dumpty",
"category" : "kids",
"author" : "steve markison"
}
{
"_id" : ObjectId("53bf49b68ac4e138b9989679"),
"title" : "Humpty Dumpty",
"category" : "laughter",
"author" : "steve markison"
}
{
"_id" : ObjectId("53bf49b68ac4e138b998967a"),
"title" : "Haunted House",
"category" : "thriller",
"author" : "george haligs"
}
{
"_id" : ObjectId("53bf49b68ac4e138b998967b"),
"title" : "Haunted House",
"category" : "horror",
"author" : "george haligs"
}

Also See:

MongoDB Aggregation – MapReduce

Aggregation Pipeline

Leave a Reply