Skip to content
Advertisement

how to find $avg and $sum for fields which contain NaN value in mongodb?

I can find and limit columns which contain NaN value before using $group clause in mongodb when I use mongo cli or JavaScript. However, when I use python and its major library “pymongo” it seems not to be able to do the same. As following code NaN is not part of python syntax. Whereas it is easy and straight forward in javaScript. Any body has same problem?

javascript

db.getCollection('plan.data_pool').aggregate([
                                     {'$match':{geoid:'00','v':{"$ne":NaN}}},{"$group":{"_id":"$accountable_id","agg":{"$sum":"$v"}}}

])

python (pymongo)

db['plan.data_pool'].aggregate([{'$match':{geoid:'00','v':{"$ne":NaN}}},
{"$group":{"_id":"$accountable_id","agg":{"$sum":"$v"}}}

sample document of data_pool collection is like this:

{
    "_id" : ObjectId("619149fa0f91f1b3954f7128"),
    "opg" : "sum",
    "opt" : "sum",
    "code" : "9-8-1-1-1-7",
    "responsible" : [ 
        "61"
    ],
    "parent_id" : "610a6abb566701e77a214355",
    "accountable_id" : "59",
    "data_item" : "610a6c49566701e77a21435c",
    "geoid" : "HQ",
    "year" : 2019 ,
    "period" : "3M",
    "tf" : 3,
    "w" : 100.0,
    "p" : 6.0,
    "v" : NaN,
    "r" : 33.3333333333333,
    "plan" : "60d2ce9cc0b1f833dbd9563a"
}

Advertisement

Answer

A NaN value in MongoDB is represented by float('NaN') in pymongo, so you can use:

{'v': {'$ne': float('NaN')}}
User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement