Querying a nested array from a document to calculate play time in hours

[Yarden_Ish-Shalom]

Hi there,
I am trying to create an aggregation to sum actual play time from documents that have a startTime and an endTime.

the data is inside an array inside a document.

what makes it tricky is that I am calculating sum of game Sessions for a few different games so I need to group by gameName/Id and produce total playtime for each game.

is this achievable ?

cument for example: 
device: 
id: "7863",
deviceSN: "378623", 

  "gameSessions": [
    {
      "deviceSN": "180322905602120",
      "endTime": 1633520201,
      "gameId": "5cf6426984c42d6c2775b6be",
      "gameName": "Lumberjacks",
      "playmode": "Wall",
      "gameEvents": [],
      "points": [
        {
          "count": 134,
          "GridColumn": 0,
          "GridRow": 0
        },
        {
          "count": 29,
          "GridColumn": 1,
          "GridRow": 0
        },
        {
          "count": 24,
          "GridColumn": 2,
          "GridRow": 0
        },
        {
          "count": 0,
          "GridColumn": 0,
          "GridRow": 1
        },
        {
          "count": 88,
          "GridColumn": 1,
          "GridRow": 1
        },
        {
          "count": 44,
          "GridColumn": 2,
          "GridRow": 1
        },
        {
          "count": 0,
          "GridColumn": 0,
          "GridRow": 2
        },
        {
          "count": 14,
          "GridColumn": 1,
          "GridRow": 2
        },
        {
          "count": 3,
          "GridColumn": 2,
          "GridRow": 2
        }
      ],
      "profile": {
        "name": "General",
        "uuid": ""
      },
      "startTime": 1633519873
    }
}

there are more fields to the object but I just need to display top 5 played games from the gameSessions array.

here is what i have so fat that produces an empty array

.aggregate([
       { $addFields: { 
                PreviousDate: { $subtract: [  new Date(), (1000*60*60*24*30) ] } 
            }
        },
        {$match:{   deviceSN: "180733444101861",
                    createdAt:{$gte:new Date("07-08-2021") , $lt: new Date("$PreviousDate")}}},
        { $unwind: "$gameSessions" },
        { $group:
               {
                 _id: "$gameSessions.gameId", // Group key
                 hoursPlayed: {$sum: { $dateDiff:{      
                     startDate: '$startTime',
                     endDate: '$endTime',
                     unit: 'hour'}}},
               }
           }

      ]).toArray()

[steevej (Steeve Juneau)]

You should $match first. This will ensure you use an appropriate index. The $addFields before the $match is potentially applied to all documents, even the ones that do not match. Yes, you use PreviousDate from your $addFields in your $match but you could do the test without having it as a field.

In your $match, you test createdAt, but I do not see this field in your sample documents. This might explain the empty array. May be the field is named differently.