Need to sum from array object value in mongodb

Question

I am trying to calculate total value if that value exits. But query is not working 100%. So can somebody help me to solve this problem. Here my sample document. I have attached two documents. Please these documents & find out best solution Document : 1

{
    "_id" : 1"),
    "message_count" : 4,
    "messages" : {
        "data" : [ 
            {
                "id" : "11",
                "saleValue": 1000
            }, 
            {
                "id" : "112",
                "saleValue": 1400
            }, 
            {
                "id" : "22",

            }, 
            {
                "id" : "234",
                "saleValue": 111
            }
        ],

    },
    "createdTime" : ISODate("2018-03-18T10:18:48.000Z")
}

Document : 2

 {
        "_id" : 444,
        "message_count" : 4,
        "messages" : {
            "data" : [ 
                {
                    "id" : "444",
                    "saleValue" : 2060
                }, 
                {
                    "id" : "444",
                }, 
                {
                    "id" : 234,
                    "saleValue" : 260
                }, 
                {
                    "id" : "34534",
                }
            ]
        },

        "createdTime" : ISODate("2018-03-18T03:11:50.000Z")
    }

Needed Output:

{
   total : 4831
}

My query :

db.getCollection('myCollection').aggregate([
    {
        "$group": {
            "_id": "$Id",

            "totalValue": {
                $sum: {
                    $sum: "$messages.data.saleValue"
                }
            }

        }
    }
])

So please if possible help me to solve this problem. Thanks in advance

Use unwind on the data array and then sum the data.value — Clement Amarnath
– Clement Amarnath, Commented Mar 26, 2018 at 12:23

chridam · Accepted Answer · 2022-03-11 13:59:09Z

8

It's not working correctly because it is aggregating all the documents in the collection; you are grouping on a constant "_id": "tempId", you just need to reference the correct key by adding the $ as:

db.getCollection('myCollection').aggregate([
    { "$group": {
        "_id": "$tempId",
        "totalValue": { 
            "$sum": { "$sum": "$messages.data.saleValue" } 
        }
    } }
])

which in essence is a single stage pipeline version of an aggregate operation with an extra field that holds the sum expression before the group pipeline then calling that field as the $sum operator in the group.

The above works since $sum from MongoDB 3.2+ is available in both the $project and $group stages and when used in the $project stage, $sum returns the sum of the list of expressions. The expression "$messages.data.value" returns a list of numbers [120, 1200] which are then used as the $sum expression:

db.getCollection('myCollection').aggregate([
    { "$project": {
        "values": { "$sum": "$messages.data.value" },
        "tempId": 1,
    } },
    { "$group": {
        "_id": "$tempId",
        "totalValue": { "$sum": "$values" }
    } }
])

edited Mar 11, 2022 at 13:59

answered Mar 26, 2018 at 12:24

chridam

104k26 gold badges246 silver badges243 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Md Nazmul Hossain Over a year ago

you query worked fine for my demo document. But not worked for my actual one. If I will share my actual data with you, can you check please ??

Md Nazmul Hossain Over a year ago

I have updated my question. Please check this & help me to figure out.

chridam Over a year ago

It's failing because "saleValue" is a string, not a number. $sum doesn't work properly with string values, try to convert the fields to numerical values before running the pipeline.

Md Nazmul Hossain Over a year ago

Need just pageId as _id & total.

chridam Over a year ago

But query is not working 100% Please show what you are getting as a result and the query you are using. Are you still using "_id": "pageId", in your $group pipeline step? If so, you need to change that to point to the correct key, like "_id": "$pageId", as explained in the above answer if you were following. Also check if your MongoDB server version >= 3.2.

|

Alejandro Montilla · Accepted Answer · 2018-03-26 12:31:45Z

1

You can add a $unwind before your $group, in that way you will deconstructs the data array, and then you can group properly:

db.myCollection.aggregate([
{
    "$unwind": "$messages.data"
},
{
    "$group": {
        "_id": "tempId",
        "totalValue": {
            $sum: {
                $sum: "$messages.data.value"
            }
        }
    }
}
])

Output:

{ "_id" : "tempId", "totalValue" : 1320 }

answered Mar 26, 2018 at 12:31

Alejandro Montilla

2,6543 gold badges34 silver badges37 bronze badges

Comments

Clement Amarnath · Accepted Answer · 2018-03-26 12:32:23Z

1

db.getCollection('myCollection').aggregate([
   {
     $unwind: "$messages.data",
     $group: {
        "_id": "tempId",
        "totalValue": { $sum: "$messages.data.value" }
     }
   }
])

$unwind

answered Mar 26, 2018 at 12:32

Clement Amarnath

5,4761 gold badge23 silver badges34 bronze badges

Comments

Rubin Porwal · Accepted Answer · 2018-03-26 17:51:32Z

1

According to description as mentioned into above question, as a solution please try executing following aggregate query

db.myCollection.aggregate(

    // Pipeline
    [
        // Stage 1
        {
            $unwind: {
                path: '$messages.data'
            }
        },

        // Stage 2
        {
            $group: {
                _id: {
                    pageId: '$pageId'
                },
                total: {
                    $sum: '$messages.data.saleValue'
                }
            }
        },

        // Stage 3
        {
            $project: {
                pageId: '$_id.pageId',
                total: 1,
                _id: 0
            }
        }

    ]


);

answered Mar 26, 2018 at 17:51

Rubin Porwal

3,8451 gold badge27 silver badges27 bronze badges

Comments

Sudip Bhujel · Accepted Answer · 2023-03-09 04:07:21Z

1

You can do it without using $group. Grouping makes other data to be managed and addressed. So, I prefer using $sum and $map as shown below:

db.getCollection('myCollection').aggregate([
 {
   $addFields: {
      total: {
          $sum: {
            $map: {
              input: "$messages.data",
              as: "message",
              in: "$$message.saleValue",
            },
          },
        },
      },
    }, 
  } 
])

edited Mar 9, 2023 at 4:07

answered Sep 7, 2022 at 8:47

Sudip Bhujel

365 bronze badges

Collectives™ on Stack Overflow

Need to sum from array object value in mongodb

5 Answers 5

9 Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

9 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related