Skip to content

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