Using $group to group data by a field, and then $sort by field within nested array

[Greg_Fitzpatrick-Bel (Greg)]

I’m attempting to group sessions by location, and then sort the session objects within the nested Sessions array by StartTime. I’ve found a way to do this using an index on StartTime, which is used in the initial $match, but I’d like a way to explicitly specify my sorting intentions. Does anyone have a way I can do this?

This how I would like the aggregation to work. Essentially the $sort stage here does nothing. Is there any way to $sort the objects pushed into Sessions in the $group stage by StartTime?

db.getCollection('sessions').aggregate([
    { 
      $match: { 
          $and: [ 
            { "StartTime": { $gte: ISODate("2020-02-01T18:52:34.000Z") } }, 
            { "StartTime": { $lte: ISODate("2020-02-02T18:52:34.000Z") } } 
          ] 
      } 
    },
    {
      $group: { 
          _id: { id: "$locationId" }, 
          numberOfSessions: { $sum: 1 },
          Sessions: { 
              $push: { 
                  _id: "$_id", 
                  SessionID: "$SessionID", 
                  StartTime: "$StartTime", 
                  EndTime: "$EndTime"
              } 
          }
      }
    },
    {
      $match: {
          numberOfSessions: { $gte: 2 }
      }
    },
    { $sort: { 'Sessions.StartTime': 1 } },
    { $project: { StartTime: -1, Sessions: -1, numberOfSessions: -1 } }
  ],
  {
     allowDiskUse: true
  } 
)

[slava]

Hello, @Greg_Fitzpatrick-Bel!

Let me show how to solve this by example.
Assume, we have this dataset:

db.sessions.insertMany([
  {
    _id: 'sesh-1',
    startTime: ISODate('2020-07-01T18:52:34.000Z'),
    endTime: ISODate('2020-08-01T18:52:34.000Z'),
    locationId: 'L2',
  },
  {
    _id: 'sesh-2',
    startTime: ISODate('2020-05-01T18:52:34.000Z'),
    endTime: ISODate('2020-06-01T18:52:34.000Z'),
    locationId: 'L1',
  },
  {
    _id: 'sesh-3',
    startTime: ISODate('2020-03-01T18:52:34.000Z'),
    endTime: ISODate('2020-04-01T18:52:34.000Z'),
    locationId: 'L1',
  },
  {
    _id: 'sesh-4',
    startTime: ISODate('2020-01-01T18:52:34.000Z'),
    endTime: ISODate('2020-02-01T18:52:34.000Z'),
    locationId: 'L2',
  },
]);

And this aggregation:

db.sessions.aggregate([
  {
    $match: {
      // match the docs anyhow you want
      // this $match stage will match all docs
      // from sessions collection
    }
  },
  {
    $group: {
      // group by $locationId
      _id: '$locationId',
      numberOfSessions: {
        $sum: 1,
      },
      sessions: {
        $push: {
          _id: '$_id',
          startTime: '$startTime',
          andTime: '$endTime',
        }
      }
    }
  },
  {
    // this is needed to sort items in $sessions array
    $unwind: '$sessions',
  },
  {
    $sort: {
      // specify $sessions sort params here
      'sessions.startTime': 1,
    }
  },
  {
    // this $group stage is needed, because we did
    // $unwind before
    $group: {
      _id: '$_id',
      numberOfSessions: {
        $first: '$numberOfSessions',
      },
      sessions: {
        $push: '$sessions',
      }
    }
  }
]).pretty();

Will provide use with this result:

[
  {
    "_id" : "L1",
    "numberOfSessions" : 2,
    "sessions" : [
      {
        "_id" : "sesh-3",
        "startTime" : ISODate("2020-03-01T18:52:34Z"),
        "andTime" : ISODate("2020-04-01T18:52:34Z")
      },
      {
        "_id" : "sesh-2",
        "startTime" : ISODate("2020-05-01T18:52:34Z"),
        "andTime" : ISODate("2020-06-01T18:52:34Z")
      }
    ]
  },
  {
    "_id" : "L2",
    "numberOfSessions" : 2,
    "sessions" : [
      {
        "_id" : "sesh-4",
        "startTime" : ISODate("2020-01-01T18:52:34Z"),
        "andTime" : ISODate("2020-02-01T18:52:34Z")
      },
      {
        "_id" : "sesh-1",
        "startTime" : ISODate("2020-07-01T18:52:34Z"),
        "andTime" : ISODate("2020-08-01T18:52:34Z")
      }
    ]
  }
]

Notice, that session objects are inserted in arbitrary order, but aggregation returned sessions, ordered by startTime.