How to Get last 5 Days Data with all Fields

Question

I am new to MongoDb and would appreciate some help with this query. I wrote the following aggregation pipeline with Get last 5 days data based on date field but small issue all data is comming how to get only 5 days data and sum of consumption any one please any one suggest me.

    {
    "timeId" : ISODate("2018-09-10T16:40:08.817Z"),
    "vendor" : "CIMCON",
    "slcId" : "123450"
    "mongoStreetLightChildVo" : {
        "deviceType" : "L",
        "totalStreetLights" : 3.0,
        "totalConsumptionMtd" : "2"
        "mongoStreetLightChildVo1s" : [ 
            {
                "date" : ISODate("2018-09-01T16:40:08.817Z"),
                "consumption" : 27.0,
                "burnHours" : 24.0
            }, 
             {
                "date" : ISODate("2018-09-02T16:40:08.817Z"),
                "consumption" : 2.0,
                "burnHours" : 24.0
            }, 
             {
                "date" : ISODate("2018-09-03T16:40:08.817Z"),
                "consumption" : 287.0,
                "burnHours" : 20.0
            }, 
             {
                "date" : ISODate("2018-09-04T16:40:08.817Z"),
                "consumption" : 209.0,
                "burnHours" : 2.0
            }, 
             {
                "date" : ISODate("2018-09-05T16:40:08.817Z"),
                "consumption" : 243.0,
                "burnHours" : 4.0
            },
             {
                "date" : ISODate("2018-09-06T16:40:08.817Z"),
                "consumption" : 254.0,
                "burnHours" : 4.0
            }, 
             {
                "date" : ISODate("2018-09-07T16:40:08.817Z"),
                "consumption" : 235.0,
                "burnHours" : 24.0
            }, 
             {
                "date" : ISODate("2018-09-08T16:40:08.817Z"),
                "consumption" : 62.0,
                "burnHours" : 24.0
            }, 
             {
                "date" : ISODate("2018-09-09T16:40:08.817Z"),
                "consumption" : 2.0,
                "burnHours" : 23.0
            }, 
             {
                "date" : ISODate("2018-09-10T02:40:08.817Z"),
                "consumption" : 26.0,
                "burnHours" : 27.0
            }
        ]
    }
}

This is my dataset.

I use this to get the Last 5 days data.

db.collection.aggregate([
{$match:{
    "mongoStreetLightChildVo.mongoStreetLightChildVo1s.date": 
    {
        $gte: new Date((new Date().getTime() - (5 * 24 * 60 * 60 * 1000)))
    }
}
},
{$project:{
   "mongoStreetLightChildVo.mongoStreetLightChildVo1s.date":1,
   "mongoStreetLightChildVo.mongoStreetLightChildVo1s.consumption":1


}}
])

how to get only 5 days data

My required output is:

  {
                "date" : ISODate("2018-09-05T16:40:08.817Z"),
                "consumption" : 243.0,
                "burnHours" : 4.0
            },
             {
                "date" : ISODate("2018-09-06T16:40:08.817Z"),
                "consumption" : 254.0,
                "burnHours" : 4.0
            }, 
             {
                "date" : ISODate("2018-09-07T16:40:08.817Z"),
                "consumption" : 235.0,
                "burnHours" : 24.0
            }, 
             {
                "date" : ISODate("2018-09-08T16:40:08.817Z"),
                "consumption" : 62.0,
                "burnHours" : 24.0
            }, 
             {
                "date" : ISODate("2018-09-09T16:40:08.817Z"),
                "consumption" : 2.0,
                "burnHours" : 23.0
            }, 
             {
                "date" : ISODate("2018-09-10T02:40:08.817Z"),
                "consumption" : 26.0,
                "burnHours" : 27.0
            }

Try `db.collection.aggregate({"$project":{"total":{"$let":{"vars":{"f":{"$filter":{"input":"$mongoStreetLightChildVo.mongoStreetLightChildVo1s","cond":{"$gte":["$$this.date",new Date((new Date().getTime() - (5 * 24 * 60 * 60 * 1000)))]}}}},"in":{"$sum":"$$f.consumption"}}}}})` — s7vr, Sep 11 '18 at 12:51
I am try this code but trow error Error: TypeError: db.STREETLIGHTSINSTANT_MONTH.aggregeate is not a function : @(shell):1:1 @ Veeram — Naresh Varun Guttula, Sep 11 '18 at 12:57
iam try this code output is { "_id" : ObjectId("5b96154a864585ef82b684a2"), "total" : 0 } /* 2 */ { "_id" : ObjectId("5b961599864585ef82b684a3"), "total" : 10.0 } iam not getting required out put my required output @ Veeram — Naresh Varun Guttula, Sep 11 '18 at 13:07
required output is:{ "date" : ISODate("2018-09-11T16:40:08.817Z"), "burnHours" : 4.0 },{ "date" : ISODate("2018-09-10T16:40:08.817Z"), "burnHours" : 8.0 },etc last 5 days data @Veeram — Naresh Varun Guttula, Sep 11 '18 at 13:08
Can you add the required output to the post ? You are asking for sum of consumption but your output shows burnHours. Which one it is ? If it is burnHours just change from consumption to burnHours in the aggregate query. — s7vr, Sep 11 '18 at 13:10
Please take some time when you post question. It is very important to get all the details right so it will save us from going back and forth. — s7vr, Sep 11 '18 at 13:13
sorry Veeram by mistake i mention required out put please check @ Veeram — Naresh Varun Guttula, Sep 11 '18 at 13:14
Its okay. Based on your output you need `db.collection.aggregate({"$project":{"data":{"$filter":{"input":"$mongoStreetLightChildVo.mongoStreetLightChildVo1s","cond":{"$gte":["$$this.date",new Date((new Date().getTime() - (5 * 24 * 60 * 60 * 1000)))]}}}}})` — s7vr, Sep 11 '18 at 13:15
iam try this code get data data like { "data" : [] } /* 2 */ { "data" : [ { "date" : ISODate("2018-09-06T16:40:08.817Z"), "consumption" : 2.0 }{ } ] } @ Veeram — Naresh Varun Guttula, Sep 11 '18 at 13:31
That is not possible. Query I have provided only filters the document. It doesn't change the format. I have verified the query and it returns the expected output. — s7vr, Sep 11 '18 at 13:48
ok thanks for supporting , in front of the output to display result this type empty data like { "data" : [] } if any possible remove this @ Veeram — Naresh Varun Guttula, Sep 11 '18 at 13:53
Yw. add the $match stage to filter the empty array after $project stage. Something like `{$match:{"data.0":{$exists:true}}}` — s7vr, Sep 11 '18 at 13:55

How to Get last 5 Days Data with all Fields

0 Answers0