mongodbgroup-byaggregation-frameworkamazonsellercentral

MongoDB Use $group for the subset after $group


I just learned mongoDB, I am trying to find some repeat customer info through my customer database. The sample collection:

{
"_id" : ObjectId("5b7617e48146d8bae"),
"amazon_id" : "112",
"date" : "2018-01-25T18:40:55-08:00",
"email" : "xxxxx@marketplace.amazon.com",
"buy_name" : "xxxxx",
"sku" : "NPC-50",
"qty" : 8,
"price" : 215.92,
"reci_name" : "XXXXX",
"street1" : "XXXXX",
"street2" : "",
"street3" : "",
"city" : "XXXXX",
"state" : "XXXXX",
"zip_code" : "XXXXXX"
 }

{
"_id" : ObjectId("5b761712e48146d8bae"),
"amazon_id" : "114",
"date" : "2018-01-27T18:40:55-08:00",
"email" : "xxxxx@marketplace.amazon.com",
"buy_name" : "xxxxx",
"sku" : "ABC",
"qty" : 1,
"price" : 19.99,
"reci_name" : "XXXXX",
"street1" : "XXXXX",
"street2" : "",
"street3" : "",
"city" : "XXXXX",
"state" : "XXXXX",
"zip_code" : "XXXXXX"
 }

I group all customer info by their email id, and here is my code:

db.getCollection('order').aggregate([

 { $group: { _id:  "$email", 
             OrderInfo: {$push: {orderId: "$amazon_id", sku: "$sku", qty: "$qty", price:"$price"
                                    }},
             CustomerInfo: {$addToSet: {buyName: "$buy_name",reName: "$reci_name", email: "$email", street1: "$street1",
                street2: "$street2", city: "$city", state: "$state", zipCode: "$zip_code"} }
             }},

 { $project: {_id: 1, OrderInfo: 1, CustomerInfo:1, total_price:{$sum: "$OrderInfo.price"}   }},
 { $match: {total_price: {$gt:100} } },
 { $sort: {total_price:-1}},

], { allowDiskUse: true }  );

It shows me the result:

 {
"_id" : "xxxxxxx@marketplace.amazon.com",
"OrderInfo" : [ 
    {
        "orderId" : "112",
        "sku" : "NPC-50",
        "qty" : 8,
        "price" : 215.92
    }, 
    {
        "orderId" : "112",
        "sku" : "NPC-50",
        "qty" : 1,
        "price" : 26.99
    }, 
    {
        "orderId" : "114",
        "sku" : "NPC-50",
        "qty" : 1,
        "price" : 26.99
    }, 
    {
        "orderId" : "114",
        "sku" : "ABC",
        "qty" : 1,
        "price" : 19.99
    }, 
    {
        "orderId" : "116",
        "sku" : "ABC",
        "qty" : 1,
        "price" : 19.99
    }, 
],
"CustomerInfo" : [ 
    {
        "buyName" : "xxxxxxxxx",
        "reName" : "xxxxxxxxxxxx",
        "email" : "xxxxxxxxxxxx@marketplace.amazon.com",
        "street1" : "xxxxxxxxxxx",
        "street2" : "",
        "city" : "xxxxxxxxxx",
        "state" : "xxxxxxxxxxxx",
        "zipCode" : "xxxxxxxxxx"
    }, 
    {
        "buyName" : "xxxxxxxxxx",
        "reName" : "xxxxxx",
        "email" : "xxxxxxxx@marketplace.amazon.com",
        "street1" : "xxxxxxxxxxx",
        "street2" : "",
        "city" : "xxxxx",
        "state" : "xxxx",
        "zipCode" : "xxxxxxxx"
    }
],
"total_price" : 309.88
}

However, I want to group the sku and sum up the qty and price in the OrderInfo Set. My expected output is something like:

     {
"OrderInfo" : [ 
    {
        "sku": "NPC-50",
        "qty": 10,
        "price": 269.9
    },
    {
        "sku": "ABC",
        "qty": 2,
        "price": 39.98
    },
],
"CustomerInfo" : [ 
    {
        "buyName" : "xxxxxxxxx",
        "reName" : "xxxxxxxxxxxx",
        "email" : "xxxxxxxxxxxx@marketplace.amazon.com",
        "street1" : "xxxxxxxxxxx",
        "street2" : "",
        "city" : "xxxxxxxxxx",
        "state" : "xxxxxxxxxxxx",
        "zipCode" : "xxxxxxxxxx"
    }, 
    {
        "buyName" : "xxxxxxxxxx",
        "reName" : "xxxxxx",
        "email" : "xxxxxxxx@marketplace.amazon.com",
        "street1" : "xxxxxxxxxxx",
        "street2" : "",
        "city" : "xxxxx",
        "state" : "xxxx",
        "zipCode" : "xxxxxxxx"
    }
],
"total_price" : 309.88
}

Any Help will be appreciated.


Solution

  • You can use below aggregation.

    db.order.aggregate([
     {"$group":{
       "_id":{"email":"$email","sku":"$sku"},
       "qty":{"$sum":"$qty"},
       "price":{"$sum":"$price"},
       "CustomerInfo":{
        "$addToSet":{
          "buyName":"$buy_name",
          "reName":"$reci_name",
          "email":"$email",
          "street1":"$street1",
          "street2":"$street2",
          "city":"$city",
          "state":"$state",
          "zipCode":"$zip_code"
         }
       }
     }},
      {"$group":{
       "_id":"$_id.email",
       "OrderInfo":{"$push":{"sku":"$_id.sku","qty":"$qty","price":"$price"}},
       "total_price":{"$sum":"$price"},
       "CustomerInfo":{"$first":"$CustomerInfo"}
     }},
     {"$match":{"total_price":{"$gt":100}}},
     {"$sort":{"total_price":-1}}
    ])