How can I select all data based on date range mongoDB?

Question

I have a lists of records like below

[
  {
    "product": "p1",
    "salesdate": "2020-02-01",
    "amount": 100
  },
  {
    "product": "p2",
    "salesdate": "2020-02-04",
    "amount": 200
  },
  
]

On 2nd feb and 3rd feb i don't have data. But I need to add this in my result. My expected result is

[
  {
    "amount": 100,
    "salesdate": "2020-02-01"
  },
  {
    "amount": 0,
    "salesdate": "2020-02-02"
  },
  {
    "amount": 0,
    "salesdate": "2020-02-03"
  }
  {
    "amount": 200,
    "salesdate": "2020-02-04"
  }
]

Can I achieve this using mongoDB?

https://mongoplayground.net/p/EiAJdY9jRHn

question from:https://stackoverflow.com/questions/66058605/how-can-i-select-all-data-based-on-date-range-mongodb

Answer 1

You can use $reduce for it. Whenever one has to work with data/time values, then I recommend the moment.js library. You don't have to use it, but it makes your life easier.

db.collection.aggregate([
   // Put all data into one document
   { $group: { _id: null, data: { $push: "$$ROOT" } } },
   // Add missing days
   {
      $addFields: {
         data: {
            $reduce: {
               // Define the range of date
               input: { $range: [0, moment().get('day')] },
               initialValue: [],
               in: {
                  $let: {
                     vars: {
                        ts: {
                           $add: [moment().startOf('month').toDate(), { $multiply: ["$$this", 1000 * 60 * 60 * 24] }]
                        }
                     },
                     in: {
                        $concatArrays: [
                           "$$value",
                           [{
                              $ifNull: [
                                 { $first: { $filter: { input: "$data", cond: { $eq: ["$$this.salesdate", "$$ts"] } } } },
                                 // Default value for missing days
                                 { salesdate: "$$ts", amount: 0 }
                              ]
                           }]
                        ]
                     }
                  }
               }
            }
         }
      }
   },
   { $unwind: "$data" },
   { $replaceRoot: { newRoot: "$data" } }
   // If requried add further $group stages
])

Note, this code returns values from first day of current months to current day (not 2020 as in your sample data). You may adapt the ranges - your requirements are not clear from the question.

Mongo Playground