MongoDB聚合可在按日期字段分组后在两个日期之间添加缺少的月份 [英] MongoDB aggregation to add missing months between two dates after grouping on date field

问题描述

我有一个查询，该查询返回按年份分组的按年份分组的集合中的条目总数.如果该位置具有所讨论的年份月份的结果，那么这将完全按照我的需要返回数据.但是，是否可以插入一个没有结果的月份的条目?例如，假设我的$ match的日期范围是01-2019到12-2019.我希望该月所有12个条目都具有默认的总计:0.这可能是

I have a query that is returning the total number of entries in a collection per year-month, grouped by a location. This is returning data exactly as I need it if the location has results for the year-month in question. However, is it possible to insert an entry for a month that does not have a result? For instance lets say if my $match has a date range of 01-2019 to 12-2019. I would like to have all 12 entries for the month with a default of total: 0. Is this possible

截断的架构:

  {
    branchId: { type: String, required: true },
    orgId: { type: String, required: true },
    stars: { type: Number, default: 0 },
    reviewUpdatedAt: { type: Date, default: Date.now }
  }

示例查询:

[
    {
        $match: {
            stars: { $exists: true, $gte: 1 },
            orgId: '100003'
            reviewUpdatedAt: { $gte: new Date(fromDate), $lte: new Date(toDate) }
        }
    },
  {
    $group: {
      _id: {
        date: {
          $dateToString: {
            format: "%m-%Y",
            date: "$reviewUpdatedAt"
          }
        },
        loc: "$branchId"
      },
      total: {
        $sum: 1
      }
    }
  },
  {
    $group: {
      _id: "$_id.loc",
      reviews: {
        $push: {
          total: "$total",
          "date": "$_id.date"
        }
      }
    }
  }
]

推荐答案

起初，我认为可以通过代码轻松实现这一点，但是即使使用MongoDB，也可以通过代码输入来实现:

At first I thought this can be easily achieved through code, but even with MongoDB you can do that but with an input from code :

假设您的 fromDate 是2018年6月& toDate 是2019年6月，然后使用您的编程语言，您可以轻松地获取这两个日期之间的所有月份，格式为 mm-yyyy .您可以尝试使用MongoDB进行此操作，但我更希望将其作为查询的输入.

Let's say if your fromDate is June-2018 & toDate is June-2019, then by using your programming language you can easily get all months between those two dates in this format mm-yyyy. You can try to do this using MongoDB but I would rather prefer as an input to query.

查询:

db.collection.aggregate([
    {
      $group: {
        _id: {
          date: {
            $dateToString: {
              format: "%m-%Y",
              date: "$reviewUpdatedAt"
            }
          },
          loc: "$branchId"
        },
        Total: {
          $sum: 1
        }
      }
    },
    {
      $group: {
        _id: "$_id.loc",
        reviews: {
          $push: {
            Total: "$Total",
            "date": "$_id.date"
          }
        }
      }
    },
    /** Overwrite existing reviews field with new array, So forming new array :: 
     * as you're passing all months between these dates get a difference of two arrays (input dates - existing dates after group)
     * while will leave us with an array of missing dates, we would iterate on that missing dates array &
     * concat actual reviews array with each missing date
     * */
    {
      $addFields: {
        reviews: {
          $reduce: {
            input: {
              $setDifference: [
                [
                  "06-2018",
                  "07-2018",
                  "08-2018",
                  "09-2018",
                  "10-2018",
                  "11-2018",
                  "12-2018",
                  "01-2019",
                  "02-2019",
                  "03-2019",
                  "04-2019",
                  "05-2019",
                  "06-2019"
                ],
                "$reviews.date"
              ]
            },
            initialValue: "$reviews",
            in: {
              $concatArrays: [
                "$$value",
                [
                  {
                    date: "$$this",
                    Total: 0
                  }
                ]
              ]
            }
          }
        }
      }
    }
  ])

测试: MongoDB-Playground

参考: javascript-get-all-两个日期之间的月份

这篇关于MongoDB聚合可在按日期字段分组后在两个日期之间添加缺少的月份的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！