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