Get sum of values from unique docs in collection of duplicate docs in MongoDB

Question

I am new to MongoDB and I am stuck in the below scenario.

I have a collection that contains duplicate docs. I just want to get the sum of the property in each doc excluding the duplicate docs.

My Docs looks like this:

{"_id":"5dd629461fc50b782479ea90",
"referenceId":"5dd581f10859d2737965d23a",
"sellingId":"319723fb80b1a297cf0803abad9bc60787537f14a6a37d6e47",
"account_name":"mrfsahas1234",
"vendor_name":"testaccount2",
"action_type":"purchase",
"product_name":"Bottle",
"product_quantity":10,
"transactionId":"319723fb80b1a297cf0803abad9bc60787537f14a6a37d6e47",
"uid":"2019-11-20T17:39:17.405Z",
"createdAt":"2019-11-21T08:56:56.589+00:00",
"updatedAt":"2019-11-21T08:56:56.589+00:00","__v":0
},
{
"_id":"5dd629461fc50b782479ea90",
"referenceId":"5dd581f10859d2737965d23a",  
"sellingId":"320a9a2f814a45e01eb98344c9af708fa2864d81587e5914",
 "account_name":"mrfsahas1234",
 "vendor_name":"testaccount2",
 "action_type":"purchase",
 "product_name":"Bottle",
 "product_quantity":50, 
 "transactionId":"320a9a2f814a45e01eb98344c9af708fa2864d81587e5914",
 "uid":"2019-11-20T17:39:17.405Z",
},
{
"_id":"5dd629461fc50b782479ea90",
"referenceId":"5dd581f10859d2737965d23a",  
"sellingId":"320a9a2f814a45e01eb98344c9af708fa2864d81587e5914",
 "account_name":"mrfsahas1234",
 "vendor_name":"testaccount2",
 "action_type":"purchase",
 "product_name":"Bottle",
 "product_quantity":50, 
 "transactionId":"320a9a2f814a45e01eb98344c9af708fa2864d81587e5914",
 "uid":"2019-11-20T17:39:17.405Z",
},

Currently, I am doing this:

MaterialsTrack.aggregate([
            {
                $match: {
                    $and: [
                        {product_name: product_name},
                        {account_name: account_name},
                        {action_type: 'purchase'},
                        {uid:uid}
                    ]
                }
            },
            {
                $group: {_id: "$sellingId", PurchseQuantity: {$sum: "$product_quantity"}}
            },
        ])

It returns the sum of product_quantity all the matching docs (including the duplicate docs). Current Output:

{_id: "320a9a2f814a45e01eb98344c9af708fa2864d81587e5914", PurchseQuantity:110}

Expected Output:

{_id: "320a9a2f814a45e01eb98344c9af708fa2864d81587e5914", PurchseQuantity:60}

I want to get the sum of only unique docs. How can I achieve it? Thanks in advance!

You need distinct values. See this https://stackoverflow.com/questions/16368638/mongodb-distinct-aggregation/35187100#35187100 — Vigen, Nov 21 '19 at 07:52

score 1 · Answer 1 · answered Nov 21 '19 at 08:10

1

What about adding $addToSet to your aggregations pipeline

MaterialsTrack.aggregate([
            {
                $match: {
                    $and: [
                        {product_name: product_name},
                        {account_name: account_name},
                        {action_type: 'purchase'},
                        {uid:uid}
                    ]
                }
            },
            {
                $group: {_id: "$sellingId", PurchseQuantity: {$sum: "$product_quantity"},"list" : {$addToSet : "$list"}}
            },
        ])

answered Nov 21 '19 at 08:10

Rebai Ahmed

1,509
1
14
21

Hey guys sorry to disturb you, but I couldn't understand how this works. In this playground it seems to return quantity 100, https://mongoplayground.net/p/nGSQK-nj8ha Am I missing something? @SaumayPaul – SuleymanSah Nov 21 '19 at 08:39
$group: {_id: "$sellingId", PurchseQuantity: {$addToSet : "$product_quantity"}} is working for me – Saumay Paul Nov 21 '19 at 08:45
@Ahmed I have updated my Question, would you mind to have a look? – Saumay Paul Nov 21 '19 at 09:48
added a new Doc in Collection and Output expectation – Saumay Paul Nov 21 '19 at 09:49

score 1 · Accepted Answer · answered Nov 21 '19 at 08:32

1

You need to sum inside of the $group _id field, and then use the replaceRoot to achieve the the result you wanted.

MaterialsTrack.aggregate([
  {
    $match: {
      $and: [
        {
          product_name: "Bottle"
        },
        {
          account_name: "mrfsahas1234"
        },
        {
          action_type: "purchase"
        },
        {
          uid: "2019-11-20T17:39:17.405Z"
        }
      ]
    }
  },
  {
    $group: {
      _id: {
        sellingId: "$sellingId",
        PurchaseQuantity: {
          $sum: "$product_quantity"
        }
      }
    }
  },
  {
    $replaceRoot: {
      newRoot: {
        _id: "$_id.sellingId",
        PurchaseQuantity: "$_id.PurchaseQuantity"
      }
    }
  }
]);

Sample Output:

[
  {
    "PurchaseQuantity": 50,
    "_id": "320a9a2f814a45e01eb98344c9af708fa2864d81587e5914"
  }
]

Playground:

https://mongoplayground.net/p/MOneCRiSlO0

answered Nov 21 '19 at 08:32

SuleymanSah

17,153
5
33
54

https://mongoplayground.net/p/NXoEdOeXSOJ - I used $group: {_id: "$sellingId", PurchseQuantity: {$addToSet : "$product_quantity"}}. And after getting result I'm accesing the data as - res['data']['PurchseQuantity'][0] – Saumay Paul Nov 21 '19 at 08:49
But I also liked your approach. Upvoting it! – Saumay Paul Nov 21 '19 at 08:50
@SaumayPaul thank you for explanation. So the accepted answer must be edited that way. Also you can shape the result by replaceRoot without doing in the app. I think my solution is better:) – SuleymanSah Nov 21 '19 at 08:54
I'm not getting the perfect result. I have updated my Question. Would you mind to have a look? – Saumay Paul Nov 21 '19 at 09:47
@SaumayPaul can you tell how can we understand if the docs are duplicate? Which field or fields must be same to be considered duplicate? – SuleymanSah Nov 21 '19 at 09:56
Okay I have achieved... Updating the answer soon – Saumay Paul Nov 21 '19 at 10:00
and I have used your method for getting unique values – Saumay Paul Nov 21 '19 at 10:02

Get sum of values from unique docs in collection of duplicate docs in MongoDB

2 Answers2