时间戳

首页 > 解决方案 > MongoDB 聚合组数组按日期

mongodb - MongoDB 聚合组数组按日期

问题描述

我有这个数据集（两个对象数组）：

_id:"PBea1d7ca5-5865-493b-bf05-40a9665de00a"
checkouts:Array
    0:Object
        date:2018-11-23 17:29:49.483
        id:"CICOf3195baf-49b4-484e-ab39-03ab1f79ca04"
waitlist:Array
    0:Object
        date:2018-11-05 21:17:02.407
        id:"WTL56ab268d-2929-493e-967b-5606d9473c18"
    1:Object
        date:2018-12-11 20:50:54.669
        id:"WTL53bc7e6a-4f29-421e-a27a-37b1c1b28735"
    2:Object
        date:2018-12-14 20:25:17.451
        id:"WTLd2efa095-cfe0-4e5e-b8bd-c369acd45306"

我需要按月和年对数组数据进行分组，结果将是一年中月份的两个文档

_id:Object
    id:"PBea1d7ca5-5865-493b-bf05-40a9665de00a"
    month:11
    year:2018
checkouts:1
waitlist:1

_id:Object
    id:"PBea1d7ca5-5865-493b-bf05-40a9665de00a"
    month:12
    year:2018
checkouts:0
waitlist:2

我试图（$unwind 然后 $group）每个数组字段，但它最终创建了重复的条目，并且总和变得相等。

_id 字段需要是年和月：

_id: {id:'$_id', month:{$month:'$groupeddata.date'}, year:{$year:'$groupeddata.date'}}，任何指针都会有所帮助。

标签： mongodbmongodb-queryaggregation-framework

解决方案

您可以使用以下聚合

db.collection.aggregate([
  { "$facet": {
    "checkouts": [
      { "$unwind": "$checkouts" },
      { "$group": {
        "_id": {
          "_id": "$_id",
          "date": { "$dateToString": { "date": "$waitlist.date", "format": "%Y-%m" }}
          }
        },
        "checkouts": { "$sum": 1 }
      }},
      { "$replaceRoot": {
        "newRoot": {
          "$mergeObjects": ["$$ROOT", "$_id"]
        }
      }}
    ],
    "waitlist": [
      { "$unwind": "$waitlist" },
      { "$group": {
        "_id": {
          "_id": "$_id",
          "date": { "$dateToString": { "date": "$waitlist.date", "format": "%Y-%m" }}
        },
        "waitlist": { "$sum": 1 }
      }},
      { "$replaceRoot": {
        "newRoot": {
          "$mergeObjects": ["$$ROOT", "$_id"]
        }
      }}
    ]
  }},
  { "$project": {
    "data": {
      "$concatArrays": ["$checkouts", "$waitlist"]
    }
  }},
  { "$unwind": "$data" },
  { "$replaceRoot": { "newRoot": "$data" }},
  { "$group": {
    "_id": { "_id": "$_id", "date": "$date" },
    "waitlist": { "$sum": "$waitlist" },
    "checkouts": { "$sum": "$checkouts" }
  }}
])

推荐阅读