get all records with recently updated attributes

Question

Hi i am new in mongo db i have an issue regarding querying data please see example below:

suppose i have this records in mongo db: collection: users;

{
 _id: 63f3456fcdd8b763618809c1,
 userName: 'john',
 status: 'created',
 updatedAt: "2023-02-21T10:29:08.032Z"
},
{
 _id: 63f3456fcdd8b763618809c2,
 userName: 'john',
 status: 'sent',
 updatedAt: "2023-02-21T10:30:16.456Z"
},
{
 _id: 63f3456fcdd8b763618809c3,
 userName: 'john',
 status: 'approved',
 updatedAt: "2023-02-21T10:30:29.157Z"
},
{
 _id: 63f3456fcdd8b763618809e1,
 userName: 'ella',
 status: 'created',
 updatedAt: "2023-02-14T07:52:48.45Z"
},
{
 _id: 63f3456fcdd8b763618809e2,
 userName: 'ella',
 status: 'sent',
 updatedAt: "2023-02-14T07:53:11.989Z"
},

i would like to create a query which returns the updated record by user. please the the following expected result:

{
 _id: 63f3456fcdd8b763618809c3,
 userName: 'john',
 status: 'approved',
 updatedAt: "2023-02-21T10:30:29.157Z"
},
{
 _id: 63f3456fcdd8b763618809e2,
 userName: 'ella',
 status: 'sent',
 updatedAt: "2023-02-14T07:53:11.989Z"
},

i can get expected result when i did the following aggregate in mongo db:

• first stage is to group users by user userName i also include userName, status and updatedAt because i want to return all record attributes. i also use $last for userName, status and updatedAt in order to display the latest value of userName, status and updatedAt.

• second stage is to sort users by updatedAt descending

db.users.aggregate([
    { 
     $group : { 
      _id : "$userName",
      userName: { $last: "$userName" },
      status: { $last: "$status" },
      updatedAt: { $last: "$updatedAt" },
     }
    },
    {
     $sort: {
      updatedAt: -1
     }
    }
]);

is there any workaround that i can dynamically display all attributes at the same time fetch only recently updated record? because whai if i have 100 attributes i will have to declare all 100 attributes to the first stage group by aggregate.

Answer 1

use $setWindowFields to compute rank for each records' ranks per user ordering by updatedAt. Then, select only record with rank: 1

db.users.aggregate([
  {
    "$setWindowFields": {
      "partitionBy": "$userName",
      "sortBy": {
        "updatedAt": -1
      },
      "output": {
        "rank": {
          "$rank": {}
        }
      }
    }
  },
  {
    $match: {
      rank: 1
    }
  },
  {
    $unset: "rank"
  }
])

Mongo Playground