Filter undesired data during mongo pipelining

Write a query to Filter undesired data during mongo pipelining.

Example:

[{ 
    "_id" : ObjectId("6020d98f62864f47ec0e1ca3")
    "mediaSite" : "facebook"
    "date" : ISODate("2021-02-05T18:30:00.000+0000")
    "hourly_View" : {
        "1" : 123.0
        "2" : 323.0
        "3" : null
        "4" : 262.0
        "5" : null
        "6" : 526.0
        "7" : 232.0
        "8" : null
        "9" : 654.0
        "10" : null
        "11" : 123.0
        "12" : 0.0
        "13" : 234.0
        "14" : 262.0
        "15" : 872.0
        "16" : 526.0
        "17" : 232.0
        "18" : 635.0
        "19" : 654.0
        "20" : 765.0
        "21" : 765.0
        "22" : -123.0
        "23" : 323.0
        "24" : 234.0
    }
}
]
Approach
Query: Find the hourly Average of view for Facebook after filtering undesired data like null values , negative values etc.

 
db.getCollection("SocialMediaTik").aggregate([
    {$match:{"mediaSite":"facebook"}},
    {$project:{"mediaSite":1,"views":{"$objectToArray":"$hourly_View"}}},
    {$unwind:"$views"},
    {$match:{"views.v":{$ne:null,$gt:0}}},
    {$group:{"_id":{"mediaSite":"$mediaSite"},"hourlyAvgView":{$avg:"$views.v"}}}
    ])


Output: 

[
        "_id" : {
            "mediaSite" : "facebook"
        }, 
        "hourlyAvgView" : 430.27777777777777
    }   
]


No comments:

Post a Comment