How project all day between two date?

Working with Data
, ,
5

Initially you said that you were using the date type:

But your sample data shows a string "2022-01-10" - now you can convert that to proper ISODate by using $toDate expression, but I’m hoping maybe it’s just a typo and the dates are already stored as dates and not strings. In either case, there are a lot of different ways this can be done, here is one way you can do what you describe (and I’m assuming you want separate documents at the end rather than an array of dates like the original input has):

Input:

db.fruit_sales.find()
{ "_id" : ObjectId("6247365a49291a2f1d0019b8"), "fruit_type" : "banana", "sale_date" : [ { "date" : ISODate("2022-01-12T00:00:00Z") }, { "date" : ISODate("2022-01-14T00:00:00Z") }, { "date" : ISODate("2022-01-15T00:00:00Z") }, { "date" : ISODate("2022-01-18T00:00:00Z") } ] }

Pipeline and result:

db.fruit_sales.aggregate([
    {$set:{
        startDate:{$min:"$sale_date.date"}, 
        endDate:{$max:"$sale_date.date"}
    }},
    {$set:{
        dates:{$map:{input:{$range:[0, {$subtract:[{$add:[1,"$endDate"]}, "$startDate"]}, 1000*60*60*24]}, in:{$add:["$startDate","$$this"]}}}
    }},
    {$unwind:"$dates"}, 
    {$project:{
        _id:0, 
        day:"$dates", 
        check_sale:{$in:["$dates", "$sale_date.date"]}
    }}
])
{ "day" : ISODate("2022-01-12T00:00:00Z"), "check_sale" : true }
{ "day" : ISODate("2022-01-13T00:00:00Z"), "check_sale" : false }
{ "day" : ISODate("2022-01-14T00:00:00Z"), "check_sale" : true }
{ "day" : ISODate("2022-01-15T00:00:00Z"), "check_sale" : true }
{ "day" : ISODate("2022-01-16T00:00:00Z"), "check_sale" : false }
{ "day" : ISODate("2022-01-17T00:00:00Z"), "check_sale" : false }
{ "day" : ISODate("2022-01-18T00:00:00Z"), "check_sale" : true }

There are many different ways to do this type of thing, this is just one of them - generate dates between start and end of the time period and then check for each date whether it’s in the array of sales dates.

Asya

4 Likes