mongoDB iterate over keys of document & sum values

Question

I'm very new to the MongoDb. With that said, my csv file data is as below which are about yearly expenses.

{"Name": "Aruba",
   "Code": "ABW",
   "Type": "Country",
   "IndicatorName": "Military_expenditure",
   "1900": 0,
   "1961": 1,
   "1962": 0,
   "1963": 0,
   "1964": 0,
   "1965": 0,
   "1966": 0,
   "1967": 0,
   "1968": 0,
   "1969": 0
}, {
   "Name": "Afghanistan",
   "Code": "AFG",
   "Type": "Country",
   "IndicatorName": "Military_expenditure",
   "1900": 0,
   "1961": 100,
   "1962": 0,
   "1963": 0,
   "1964": 0,
   "1965": 0,
   "1966": 0,
   "1967": 0,
   "1968": 0,
   "1969": 0
}

However, I need to get the summation of

Total yearly expenses, that is

Aruba=1 >> (1900=0 + 1961=1......+ 1969=0} Afghanistan = 100 >> (1900=0 + 1961=100......+ 1969=0}

Total Expense of the countries =101

Aruba (1900=0 + 1961=1......+ 1969=0} + Afghanistan(1900=0 + 1961=100......+ 1969=0}

can someone please help to do the above calculations in MongoDb

However, I have written query to get the year wise summation

db.MiltryExpenditure.aggregate([

 { $match: { "Type":"Country" } },

 {$group:{_id : null,
 1969: { $sum: { "$toDouble":"$1969" }}
, _id : null,
 1960: { $sum: { 

"$toDouble":"$1960" }},
}}
])

But I don't know how to get the Total summation of the countries, as well as if theirs a normalize method to get the country wise summation , that would be much appreciated.

Please Help...

Do you only have those years or more random n no.of years ? Also how many other fields do you've ? maybe this has to be or can be easily done using language code..!! — whoami - fakeFaceTrueSoul
– whoami - fakeFaceTrueSoul, Commented Jan 4, 2020 at 8:17
I have the years from 1900-2018 & 265 rows(countries), I have to accomplish this task using mongo commands — Udk
– Udk, Commented Jan 4, 2020 at 8:27
Do you've duplicate documents with "Name": "Afghanistan" or "Name": "Aruba" like that ? — whoami - fakeFaceTrueSoul
– whoami - fakeFaceTrueSoul, Commented Jan 4, 2020 at 8:35

whoami - fakeFaceTrueSoul · Accepted Answer · 2020-01-04 10:13:45Z

2

Please try this :

db.yourCollection.aggregate([{ $match: { "Type": "Country" } },
{ $project: { _id: 0, Code: 0, IndicatorName: 0, Type: 0 } },
{
    $addFields: {
        onlyYears: {
            $filter: {
                input: { $objectToArray: "$$ROOT" },
                as: "item",
                cond: { $ne: ["$$item.k", 'Name'] }
            }
        }
    }
}, {
    $project: {
        Name: 1, count: {
            $reduce: {
                input: '$onlyYears',
                initialValue: 0,
                in: { $add: ["$$value", {"$toDouble": "$$this.v"}] }
            }
        }
    }
},
{ $group: { _id: '', count: { $sum: '$count' }, data: { $push: '$$ROOT' } } }
])

Result :

/* 1 */
{
    "_id" : "",
    "count" : 101.0,
    "data" : [ 
        {
            "Name" : "Aruba",
            "count" : 1.0
        }, 
        {
            "Name" : "Afghanistan",
            "count" : 100.0
        }
    ]
}

edited Jan 4, 2020 at 10:13

answered Jan 4, 2020 at 9:06

whoami - fakeFaceTrueSoul

18.1k6 gold badges36 silver badges51 bronze badges

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

10 Comments

Udk Over a year ago

this gives me an syntax error saying that $add only support numeric or data type not strings, I believe this error comes because CSV data uploaded to the collection recognize the expense value as string. can you please help to include the conversion from string to double as well

whoami - fakeFaceTrueSoul Over a year ago

@Udk : Updated code w.r.t. string values for expenses!!

whoami - fakeFaceTrueSoul Over a year ago

Do you mean Name inside data ? Can you give me your result here..

Udk Over a year ago

This is the result I get { "_id" : "", "count" : 3240672793.3, "data" : [ { "Name" : "0", "count" : 1 }, { "Name" : "0", "count" : 3240672792.3 } ] }

whoami - fakeFaceTrueSoul Over a year ago

@Udk : try my answer in chat room

|

mickl · Accepted Answer · 2020-01-04 09:04:20Z

$objectToArray allows you to convert your $$ROOT object into an array of keys and values. Then you can apply $filter on that array to get only those pairs representing years. Once the data set is limited to years you can run $unwind in order to execute $group on every year separately:

db.collection.aggregate([
    {
        $project: {
            _id: 0,
            years: { 
                $filter: { 
                    input: { $objectToArray: "$$ROOT" }, 
                    cond: { $and: [ { $gte: [ "$$this.k", "1900" ] }, { $lte: [ "$$this.k", "2020" ] } ] } 
                } 
            }
        }
    },
    {
        $unwind: "$years"
    },
    {
        $group: {
            _id: "$years.k",
            total: { $sum: "$years.v" }
        }
    },
    {
        $sort: { _id: 1 }
    }
])

Mongo Playground

Grouping by country is easier, you can run $sum twice (to sum up all years by doc and then inside $group):

db.collection.aggregate([
    {
        $project: {
            _id: 0,
            Name: 1,
            years: { 
                $filter: { 
                    input: { $objectToArray: "$$ROOT" }, 
                    cond: { $and: [ { $gte: [ "$$this.k", "1900" ] }, { $lte: [ "$$this.k", "2020" ] } ] } 
                } 
            }
        }
    },
    {
        $group: {
            _id: "$Name",
            total: { $sum: { "$sum": "$years.v" } }
        }
    }
])

Mongo Playground (2)

EDIT: second query can be shortened if there's single document per country (you can get rid of $group):

db.collection.aggregate([
    {
        $project: {
            _id: 0,
            Name: 1,
            Total: {
                $let: {
                    vars: { years: { 
                        $filter: { 
                            input: { $objectToArray: "$$ROOT" }, 
                            cond: { $and: [ { $gte: [ "$$this.k", "1900" ] }, { $lte: [ "$$this.k", "2020" ] } ] } 
                        } 
                    } },
                    in: { $sum: "$$years.v" }
                }
            }
        }
    }
])

Ashish Bakwad · Accepted Answer · 2020-01-04 09:41:45Z

1

you can try like this,

    db.MiltryExpenditure.aggregate(
   [{
        "$match": {
            "Type": "Country"
        }
    },
    {
        "$group": {
            "_id": null,

            "1969": {
                "$sum": {
                    "$toDouble": "$1969"
                }
            },

            "1960": {
                "$sum": {
                    "$toDouble": "$1960"
                }
            },

            "totalSummation": {
                "$sum": {
                    "$add": [{
                        "$toDouble": "$1960"
                    }, {
                        "$toDouble": "$1961"
                    }]
                }
            }
        }
    }
])

edited Jan 4, 2020 at 9:41

answered Jan 4, 2020 at 9:02

Ashish Bakwad

8115 silver badges16 bronze badges

3 Comments

Udk Over a year ago

this gives me an error saying "Syntax error : missing ] after element list My syntax db.MiltryExpenditure.aggregate( [ { "$match": { "Type": "Country" } }, { "$group": {"_id": null,"1969": { "$sum": {"$toDouble": "$1969" }}, "1960": { "$sum": {"$toDouble": "$1960"}}, "totalSummation": {"$sum": {"$add": ["$toDouble":"$1960","$toDouble":"$1961" ]}} } } ]);

Ashish Bakwad Over a year ago

Corrected answer check now.

Udk Over a year ago

Would it be possible to you to suggest, How could I get the top 10 values from this query

Collectives™ on Stack Overflow

mongoDB iterate over keys of document & sum values

3 Answers 3

10 Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

10 Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related