How to create incremental range aggregation in mongoose from a query parameter. i.e. create an incremental range from a field value

user_19788522 0
Viewed 28

So the data set looks like this: screenshot of the data structure

{
  "YearWeekISO": "2020-W53",
  "FirstDose": 0,
  "FirstDoseRefused": "",
  "SecondDose": 0,
  "DoseAdditional1": 0,
  "DoseAdditional2": 0,
  "UnknownDose": 0,
  "NumberDosesReceived": 0,
  "NumberDosesExported": 0,
  "Region": "AT",
  "Population": "8901064",
  "ReportingCountry": "AT",
  "TargetGroup": "ALL",
  "Vaccine": "JANSS",
  "Denominator": 7388778
}, {
  "YearWeekISO": "2020-W53",
  "FirstDose": 0,
  "FirstDoseRefused": "",
  "SecondDose": 0,
  "DoseAdditional1": 0,
  "DoseAdditional2": 0,
  "UnknownDose": 8,
  "NumberDosesReceived": 0,
  "NumberDosesExported": 0,
  "Region": "AT",
  "Population": "8901064",
  "ReportingCountry": "AT",
  "TargetGroup": "ALL",
  "Vaccine": "UNK",
  "Denominator": 7388778
},

link to the data set

The query parameters will look like :

GET /vaccine-summary?c=AT&dateFrom=2020-W10&dateTo=2020-W53&range=5

where

c, country code to get report for

dateFrom, yyyy-Www, eg. 2020-W10 (Including)

dateTo, yyyy-Www, eg, 2020-W20 (Excluding)

rangeSize, number, eg, the period for which to calculate metrics

After applying the aggregation, you should have a transformed data set that looks like :

{
  "summary": [{
      "weekStart": "2020-W10",
      "weekEnd": "2020-W15",
      "NumberDosesReceived": 1000
    },
    {
      "weekStart": "2020-W15",
      "weekEnd": "2020-W20"
      "NumberDosesReceived": 2000
    }, …
    till end of range(dateTo)
  ]
}

}

Notice how the weekStart incremental from 2020-W10 to 2020-W15, similar with weekEnd.

NumberDosesReceived is the sum of NumberDosesReceived fileld within that range

mongoose node.js mongodb javascript
1 Answers

It should work

const YearWeekISO = { $toDate: "$YearWeekISO" };

{
        $project: {
          fiveWeekperiod: {
            $subtract: [
              { $week: YearWeekISO },
              { $mod: [{ $week: YearWeekISO }, 5] },
            ],
          },
          date: YearWeekISO,
          NumberDosesReceived: 1,
        },
      },
      {
        $group: {
          _id: {
            year: { $year: "$date" },
            fiveWeek: "$fiveWeekperiod",
          },
          weekStart: { $min: "$date" },
          weekEnd: { $max: "$date" },
          NumberDosesReceived: { $sum: "$NumberDosesReceived" },
        },
      },
      {
        $project: {
          _id: 0,
          weekStart: {
            $dateToString: {
              date: "$weekStart",
              format: "%G-W%V",
            },
          },
          weekEnd: {
            $dateToString: {
              date: {
                $dateAdd: {
                  startDate: "$weekEnd",
                  unit: "week",
                  amount: 1,
                },
              },
              format: "%G-W%V",
            },
          },
          NumberDosesReceived: 1,
        },
      }

user_178176010
Related