CODEWITHSUNDEEP
mongodbaggregation-framework
user
user

Reputation: 51

mongodb - filter out some values when doing $unwind

I have the following Customer Order data in mongodb

 "_id" : 7,
 "customer name" : "John Smith",
 "OrderItem" : [
         {
                 "product_category" : "Mobile",
                 "price" : 900
         },
         {
                 "product_category" : "Computer",
                 "price" : 4200.48
         },
         {
                 "product_category" : "TV",
                 "price" : 670.20
         },
         {
                 "product_category" : "TV",
                 "price" : 960.52
         }
 ]

I need to average each product category to be like this:

 "_id" : 7,
 "customer name" : "John Smith",
 "OrderItem" : [
         {
                 "product_category" : "Mobile",
                 "price" : 900
         },
         {
                 "product_category" : "Computer",
                 "price" : 4200.48
         },
         {
                 "product_category" : "TV",
                 "price" : 815.36
         }
 ]

i tried to use $unwind but not sure how to group them . any help ?

Upvotes: 5

Views: 8698

Answers (3)

evilive
evilive

Reputation: 1869

.aggregate([
    {$unwind: "$OrderItem"},
    {$group: {
        _id: {id: "$_id", cat: "$OrderItem.product_category"}, 
        name: {$first: "$customer name"}, 
        price: {$avg: "$OrderItem.price"}
    }}, 
    {$group: {
        _id: "$_id.id", 
        OrderItem: {$push: {product_category: "$_id.cat", price: "$price"}}, 
        "customer name": {$first: "$name"}
    }}
])

Upvotes: 0

chridam
chridam

Reputation: 103335

Use aggregation framework with a pipeline which consists of the following stages: a $match operation in the first pipeline stage filters the document stream to allow only matching documents (document with _id = 7 in your case) to pass unmodified into the next pipeline stage, which is the $unwind operation. This deconstructs the desired OrderItem array field from the input documents to output a document for each element that you can then group on and do the aggregation operation of finding the average of the category prices. The next stage in the pipeline is the $group operation which then groups input documents by product_category and applies the $avg expression to each group on the price. The last stage $project then reshapes each document in the stream to produce the desired outcome. Thus your aggregation would look like:

db.collection.aggregate([
    {
        "$match": {"_id": 7}
    },
    {   
        "$unwind": "$OrderItem"
    },
    {
        "$group": {
            "_id": "$OrderItem.product_category",
            "average_price": {
                "$avg": "$OrderItem.price"
            }
        }
    },
    {
        "$project": {
            "_id": 0,
            "product_category" : "$_id",
            "average_price": 1
        }

    }
])

Result:

{
    "result" : [ 
        {
            "average_price" : 4200.48,
            "product_category" : "Computer"
        }, 
        {
            "average_price" : 815.36,
            "product_category" : "TV"
        }, 
        {
            "average_price" : 900,
            "product_category" : "Mobile"
        }
    ],
    "ok" : 1
}

Upvotes: 2

Neo-coder
Neo-coder

Reputation: 7840

First you should unwind OrderItem then group them and mongo $avg to calculate avarage. Below aggregation will calculate avg 

    db.collectionName.aggregate(
                    {"$match":{"customer name":"John Smith"}}, // match specified  customername  
                    {"$unwind":"$OrderItem"}, // unwind the OrderItem

                    {"$group":{"_id":"$OrderItem.product_category",
                       "avg":  {"$avg":"$OrderItem.price"} // mongo avg method used for avrage
                }}
              ).pretty()

So above query return following results

{ "_id" : "Computer", "avg" : 4200.48 }
{ "_id" : "TV", "avg" : 815.36 }
{ "_id" : "Mobile", "avg" : 900 }

But above result not match your given expected output, so you should group twice to get exact output 

        db.collectionName.aggregate(
                {"$match":{"customer name":"John Smith"}},  //match given criteria
                {"$unwind":"$OrderItem"},           //unwind $OrderItem

                {"$group":{"_id":"$OrderItem.product_category",
                           "customerName":{"$first":"$customer name"}, // group all data with calculating avg
                           "id":{"$first":"$_id"}, 
                               "avg":{"$avg":"$OrderItem.price"}}},

                    {"$group":{"_id":"$id",
                       "customer Name":{"$first":"$customerName"},
                           "OrderItem":{"$push":   {"product_category":"$_id","price":"$avg"}}}} // group them for expected output

               ).pretty()

Upvotes: 0

Related Questions