How to get data as a array in mongo db aggregration

Question

Suppose this is my data schema

{
    "_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")
}

I am doing this following aggregation:

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'}
                                       }}
])

By doing this I am able to achieve this output:

{'_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}

But is there any way I can get all the points in an array like this desired following output, where I single query returns an array of the daily attendance_points along with its corresponding date:

 {'_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

 ]}

Or something like this:

{'_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

     ]}

turivishal · Accepted Answer · 2020-10-26 07:19:58Z

You can create an array inside $group using $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" }
    }
  }

Playground

If you want an object then you can try,

$push k(key) and v(value), convert key as string type because its date type using $toString
$addFields to convert array to object using $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"
      }
    }
  }

Playground

Collectives™ on Stack Overflow

How to get data as a array in mongo db aggregration

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related