My collection, userresults, has documents which are unique by userref and sessionref together. A session has a selection of game results in a results array. I have already filtered the results to return those userresults documents which contain a result for game “Clubs”.

[{
    "userref": "AAA",
    "sessionref" : "S1",
    "results": [{
        "gameref": "Spades",
        "dateplayed": ISODate(2022-01-01T10:00:00),
        "score": 1000
    }, {
        "gameref": "Hearts",
        "dateplayed": ISODate(2022-01-02T10:00:00),
        "score": 500
    }, {
        "gameref": "Clubs",
        "dateplayed": ISODate(2022-01-05T10:00:00),
        "score": 200
    }]
}, {
    "userref": "AAA",
    "sessionref" : "S2",
    "results": [{
        "gameref": "Spades",
        "dateplayed": ISODate(2022-02-02T10:00:00),
        "score": 1000
    }, {
        "gameref": "Clubs",
        "dateplayed": ISODate(2022-05-02T10:00:00),
        "score": 200
    }]
}, {
    "userref": "BBB",
    "sessionref" : "S1",
    "results": [{
        "gameref": "Clubs",
        "dateplayed": ISODate(2022-01-05T10:00:00),
        "score": 200
    }]
}]

What I need to do within my aggregation is select the userresult document FOR EACH USER that contains the most recently played game of Clubs, ie in this case it will return the AAA/S2 document and the BBB/S1 document.

I’m guessing I need a group on the userref as a starting point, but then how do I select the rest of the document based on the most recent Clubs date?

Thanks!

CodePudding user response：

If I've understood correctly you can try this aggregation pipeline:

• First I've used $filter to avoid $unwind into the entire collection. With this you can get only objects into the results array where the gameref is Clubs.
• Next stage is now the $unwind but in this case only with remaining documents, not the entire collection. Note that this stage will not pass to the next stage documents where there is no any "gameref": "Clubs".
• Now $sort the remaining results by dateplayed to get the recent date at first position.
• And last $group using $first to get the data you want. As documents are sorted by dateplayed, you can get desired result.

db.collection.aggregate([
  {
    "$set": {
      "results": {
        "$filter": {
          "input": "$results",
          "cond": {
            "$eq": [
              "$$this.gameref",
              "Clubs"
            ]
          }
        }
      }
    }
  },
  {
    "$unwind": "$results"
  },
  {
    "$sort": {
      "results.dateplayed": -1
    }
  },
  {
    "$group": {
      "_id": "$userref",
      "results": {
        "$first": "$results"
      },
      "sessionref": {
        "$first": "$sessionref"
      }
    }
  }
])

Example here