How to add missing dates to data

Geenzie · August 13, 2021, 9:22am

Hi, I’m pretty new to mongodb aggregate. I’m trying to graph the number of orders that I have by the date the order was created, but I would like to be able to includes dates that have no orders and have orders be equal to 0 on those days. Since these dates technically don’t exist, I’ve been been struggle to do this. I was wondering if there was a way to add/create these dates in mongodb aggregate. Below, I have included what I have so far. I would really appreciate any help or guidance on how to solve this. Thank you!

orderRouter.get(
'/summary',
isAuth,
isAdmin,
expressAsyncHandler(async (req, res) => {

const dailyOrders = await Order.aggregate([
     {
       $group: {
         _id: { day: {$dateToString: {
           format: '%m-%d-%Y',
           date: '$createdAt' },
         },
       },
         orders: { $sum: 1},
      },
    },
     { $sort: { _id: 1 } },
     {$project: {
       day: {
           $cond:{
            if:{$exist: true}, 
             then: {dy: {$dateToString:{
                 format: '%m-%d-%Y',
                 date: '$_id.day' },
             },
             else: {$add:[{$first:'$dy'}, 2*24*60*60000]}
           },
           },
         },
       orders: '$orders'
     }},
   ]);

res.send({ dailyOrders });
 })
);

What I originally had:

 orderRouter.get(
 '/summary',
 isAuth,
 isAdmin,
 expressAsyncHandler(async (req, res) => {

    const dailyOrders = await Order.aggregate([
      {
        $group: {
          _id: { $dateToString: { format: '%m-%d-%Y', date: '$createdAt' } },
          orders: { $sum: 1 },
        },
      },
      { $sort: { _id: 1 } },
    ]);

 res.send({ dailyOrders });
  })
);

Takis · August 13, 2021, 11:05am

Hello and welcome here : )

I remember you asked on stackoverflow also and i sended you a link with an answer
How to add missing dates, to a similar question.

Try it and if it doesn;t work give us some data so we can make you a query based on them.

Maybe there is a better way to do this, but check the link, its full example with data in,queries,and results, it creates a dates collection(you do it once only) and makes a $lookup with the orders.

If you can give us

data in (small part of data, but big enough so we can test the query)
what you want to do(or/and you query) (you did this part)
data out (after the query,expected results)

steevej · August 13, 2021, 12:20pm

Personally, I would implement the idea expressed in Takis’ link at the application level in the code you shown above, rather than having the database server do it. I would use an array of dates, for all dates of interest, with a default order count of 0. I would then take dailyOrders to complete the array of dates with the count of orders received.

Why?

It reduces the workload of the server.
It reduces the bandwidth needs between the server and the application.
It increases the chances to distribute this workload to the multiple application instances.
It allows to have a more flexible API because you could have one /summary-non-zeros and /summary-with-zeros rather than only /summary. You would use /summary-with-zeros to provide an enriched result for /summary-with-zeros.

Takis · August 13, 2021, 12:31pm

I think its better also, i tried this yesterday, to add the dates on the app after getting the results.

Add missing dates with Javascript but i didn’t complete it to be a runnable example, maybe someone can complete it or fix it/change it, to make it work.

Geenzie · August 13, 2021, 11:30pm

Hi Takis, Thank you for your response, I’m a bit confused on what you did.

The first thing I was unsure of was if this was all supposed to be in one collection:

db.collection.countDocuments ([
  [
    {
      "date": 0
    },
    {
      "date": 1
    },
    {
      "date": 2
    },
    {
      "date": 3
    },
    {
      "date": 4
    },
    {
      "date": 5
    },
    {
      "date": 6
    },
    {
      "date": 7
    },
    {
      "date": 8
    },
    {
      "date": 9
    }
  ],
  {
    "update": "dates",
    "updates": [
      {
        "q": {},
        "u": [
          {
            "$addFields": {
              "date": {
                "$add": [
                  "2021-08-01T00:00:00Z",
                  {
                    "$multiply": [
                      "$date",
                      24,
                      60,
                      60000
                    ]
                  }
                ]
              }
            }
          }
        ],
        "multi": true
      }
    ]
  },
  {
    "aggregate": "dates",
    "pipeline": [
      {
        "$match": {
          "$expr": {
            "$and": [
              {
                "$gte": [
                  "$date",
                  {
                    "$dateFromString": {
                      "dateString": "2021-08-01T00:00:00"
                    }
                  }
                ]
              },
              {
                "$lte": [
                  "$date",
                  {
                    "$dateFromString": {
                      "dateString": "2021-08-05T00:00:00"
                    }
                  }
                ]
              }
            ]
          }
        }
      },
      {
        "$lookup": {
          "from": "orders",
          "let": {
            "datesDate": "$date"
          },
          "pipeline": [
            {
              "$match": {
                "$expr": {
                  "$eq": [
                    {
                      "$dateToString": {
                        "format": "%Y-%m-%d",
                        "date": "$$datesDate"
                      }
                    },
                    {
                      "$dateToString": {
                        "format": "%Y-%m-%d",
                        "date": "$date"
                      }
                    }
                  ]
                }
              }
            }
          ],
          "as": "found-orders"
        }
      },
      {
        "$project": {
          "_id": 0
        }
      }
    ],
    "cursor": {},
    "maxTimeMS": 1200000
  }
  
]);

Also, is all of this supposed to be within my:

rderRouter.get(
 '/summary',
 isAuth,
 isAdmin,
 expressAsyncHandler(async (req, res) => {

}

The formatting was also a bit confusing for me. In the way I had been formatting my code, something like $addFields would be written without quote, but in your code you use quotes ("$addFields"). Why is that? Similarly, I had been within the Order.aggregate([ ]) method not the db.collection.countDocuments ([ ]) method, is it necessary to use db.collection.countDocuments ([ ])? Why?

And lastly, since I don’t have a specific start date (it all depends on when the first order was made), would I supplement '$createdAt' where "2021-08-01T00:00:00Z" was within the "$add" array? Unlike the example, I also don’t have a specific start and end date, so should I still include the code after "$match", and if so, what dates would I include for the "$gte " and "$lte" ? $first: { '$ceatedAt' } and $last: { '$ceatedAt' } ?

Takis · August 14, 2021, 1:52am

The solution that i gave i dont know if its good one, i still believe that its better to be done in javascript and not in the server like steevej said, but if you want to know what i did.

i created a collection with dates, with difference 1 day (1 document = 1 day) (step 1,2)
you can make a collection with 1 year or more its nothing to have 365 documents collection
i joined($lookup) that collection with the orders collections, and put all the orders that made that day to an array (step 3)

I used the JSON syntax not the Javascript object syntax its the same, but JSON is the common way to write queries that can be used across many languages.

If you are new in MongoDB that queries might look complicated and can make you more confused.If you can try the javascript way to add the dates after you get the data from the server.

I hope you find a way to do it.