时间戳

首页 > 解决方案 > 如何在 mongo db 聚合中将数据作为数组获取

arrays - 如何在 mongo db 聚合中将数据作为数组获取

问题描述

假设这是我的数据模式

{
    "_id" : ObjectId("5f90ed2954d61feed1720603"),
    "date" : ISODate("2020-10-22T00:00:00Z"),
    "worker_full_name" : "JOHN DOE",
    "worker_department" : "Worker Department",
    "type" : "Worker",
    "site_name" : "DL LIMITED",
    "in_time" : ISODate("2020-10-22T07:53:35Z"),
    "attendance_points" : 2,
    "out_time" : ISODate("2020-10-22T22:03:41Z"),
    "duration" : 14,
    "attendance_count" : 2,
    "wage" : 580,
    "in_location" : "Countryside Avenue",
    "worker_aadhar_card_number" : "xxxxxxxxxxxx",
    "out_location" : "Golf Drive",
    "worker_id" : ObjectId("5f12ea794fdb64e82ce68fac")
}
{
    "_id" : ObjectId("5f90ed2754d61feed17205fe"),
    "date" : ISODate("2020-10-22T00:00:00Z"),
    "worker_full_name" : "JOHN DOE 2",
    "worker_department" : "Worker Department",
    "type" : "Worker",
    "site_name" : "DL LIMITED",
    "in_time" : ISODate("2020-10-22T07:53:34Z"),
    "attendance_points" : 2,
    "out_time" : ISODate("2020-10-22T22:24:02Z"),
    "duration" : 14,
    "attendance_count" : 2,
    "wage" : 0,
    "in_location" : "Countryside Avenue",
    "worker_aadhar_card_number" : "xxxxxxxxxxxx",
    "out_location" : "Countryside Avenue",
    "worker_id" : ObjectId("5f688cf2df29927bfb8531eb")
}

我正在执行以下聚合：

my_collection.aggregate([{'$match': {'date': {"$gte": start_date, "$lte": end_date},
                                                   'worker_full_name': {"$exists": 'true'},
                                                   "site_name": site_name
                                                   }},
                                       {"$group": {'_id': {
                                           'worker_id': '$worker_id',
                                           'worker_full_name': '$worker_full_name'
                                       },
                                           'present_days': {'$sum': 1},
                                           'total_shift_points': {'$sum': '$attendance_points'}
                                       }}
])

通过这样做，我能够实现这个输出：

{'_id': {'worker_id': ObjectId('5f688cf2df29927bfb8531d6'), 'worker_full_name': 'JOHN DOE'}, 'present_days': 22, 'total_shift_points': 38.25}
{'_id': {'worker_id': ObjectId('5f66130f94c75522f314dbc0'), 'worker_full_name': 'JOHN DOE 2'}, 'present_days': 19, 'total_shift_points': 35.25}
{'_id': {'worker_id': ObjectId('5f66130e94c75522f314db99'), 'worker_full_name': 'JOHN DOE 3'}, 'present_days': 23, 'total_shift_points': 42.75}
{'_id': {'worker_id': ObjectId('5f27b678749921225e5df98c'), 'worker_full_name': 'JOHN DOE 4 '}, 'present_days': 22, 'total_shift_points': 38.25}
{'_id': {'worker_id': ObjectId('5f6f2ac0b112533f081c3bae'), 'worker_full_name': 'JOHN DOE 5'}, 'present_days': 21, 'total_shift_points': 36.75}

但是有什么方法可以让我得到一个数组中的所有点，就像下面这个期望的输出一样，我单个查询返回一个每日出席点数及其相应日期的数组：

 {'_id': {'worker_id': ObjectId('5f688cf2df29927bfb8531d6'),
  'worker_full_name': 'JOHN DOE'},
  'present_days': 22,
  'total_shift_points': 38.25 ,
  'daily_points_stats':[
                {ISODate("2020-10-01T00:00:00Z"):2},
                {ISODate("2020-10-02T00:00:00Z"):1.5}
                {ISODate("2020-10-03T00:00:00Z"):1}
                {ISODate("2020-10-04T00:00:00Z"):1.25}
                ..
                ..for all the days

 ]}

或者是这样的：

{'_id': {'worker_id': ObjectId('5f688cf2df29927bfb8531d6'),
      'worker_full_name': 'JOHN DOE'},
      'present_days': 22,
      'total_shift_points': 38.25 ,
      'daily_points_stats':[
                    {"date":ISODate("2020-10-01T00:00:00Z"),"points":2},
                    {"date":ISODate("2020-10-02T00:00:00Z"),"points":1.5},
                    {"date":ISODate("2020-10-03T00:00:00Z"),"points":1},
                    {"date":ISODate("2020-10-04T00:00:00Z"),"points":1.25},
                    ..
                    ..for all the days

     ]}

标签： arraysdatabasemongodbaggregation-frameworkaggregate-functions

解决方案

$group您可以使用在内部创建一个数组$push，

  {
    "$group": {
      "_id": {
        "worker_id": "$worker_id",
        "worker_full_name": "$worker_full_name"
      },
      // like this
      daily_points_stats: {
        $push: {
          date: "$date",
          points: "$attendance_count"
        }
      },
      "present_days": { "$sum": 1 },
      "total_shift_points": { "$sum": "$attendance_points" }
    }
  }

操场

如果你想要一个对象，那么你可以尝试，

  • $push k(key) 和 v(value)，将 key 转换为字符串类型，因为它的日期类型使用$toString
  • $addFields使用将数组转换为对象$arrayToObject
  {
    "$group": {
      "_id": {
        "worker_id": "$worker_id",
        "worker_full_name": "$worker_full_name"
      },
      daily_points_stats: {
        $push: {
          k: { $toString: "$date" },
          v: "$attendance_count"
        }
      },
      "present_days": { "$sum": 1 },
      "total_shift_points": { "$sum": "$attendance_points" }
    }
  },
  {
    $addFields: {
      daily_points_stats: {
        $arrayToObject: "$daily_points_stats"
      }
    }
  }

操场

推荐阅读