Choose single document based on dates in subdocument

Working with Data
1

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 Clubs date?

Thanks!

3

Hi @Fiona_Lovett1 and welcome to MongoDB community forum!!

Based on the above example document shared, the below query might be helpful in achieving the desired output:

db.collection.aggregate([
     {
       '$unwind': {
         'path': '$results'
       }
     }, {
       '$match': {
         'results.gameref': 'Clubs'
       }
     }, {
       '$group': {
         '_id': {
           'user': '$userref',
           'gameref': '$results.gameref'
         },
         'mostrecentdate': {
           '$max': '$results.dateplayed'
         }
       }
     }
   ])
[
  {
    _id: { user: 'AAA', gameref: 'Clubs' },
    mostrecentdate: ISODate("2022-05-02T04:30:00.000Z")
  },
  {
    _id: { user: 'BBB', gameref: 'Clubs' },
    mostrecentdate: ISODate("2022-01-05T04:30:00.000Z")
  }
]

However, please note that, the above query has only been tested on the sample documents provided. I would recommend testing thoroughly on your own test environment to verify it suits all your use case and requirements before running against any production data. collection.

Let us know if you have any further queries.

Best Regards
Aasawari

1 Like