sorry but i'm just really bad at aggregate. Sample 4 JSONs which represent playerMovements in a game, for two different userIds are below. I need to get their latest movement in the game based on the userId.

You can see that for the four records, two each for both ids. I just want the latest _id for each userId to be returned. (basically from a use case standpoint, i want the latest moveX, moveY, moveZ for the userId)

[
  {
    "_id": "6288cf05279219e36338b5d2",
    "moveX": "0.1174682",
    "moveY": "0",
    "moveZ": "0.02936705",
    "userId": "6288ceea279219e36338b5bc",
    "__v": 0
  },
  {
    "_id": "628a5792df1d8588afb4f407",
    "moveX": "-1.574591",
    "moveY": "0",
    "moveZ": "0.08151396",
    "userId": "6288cf3a279219e36338b5f3",
    "__v": 0
  },
  {
    "_id": "6288f240f424266b6b3f693a",
    "moveX": "0.1",
    "moveY": "0.5351",
    "moveZ": "0.03682599",
    "userId": "6288ceea279219e36338b5bc",
    "__v": 0
  },
  {
    "_id": "628af9a8a00e76a4f0c3b6d8",
    "moveX": "0.5",
    "moveY": "0.12",
    "moveZ": "0.5",
    "userId": "6288cf3a279219e36338b5f3",
    "__v": 0
  }
]

I need to group by userId and return the last inserted record. Thanks in advance!

CodePudding user response：

I think this should work.

.aggregate([
{
    $group: {
      _id: { userId : "$userId" },
      lastMoveX: { $last: "$moveX" },
      lastMoveY: { $last: "$moveY" },
      lastMoveZ: { $last: "$moveZ" }
    }
}]);

CodePudding user response：

Final one that i am using that works.Also seems simpler and more complete

https://mongoplayground.net/p/ku2ClKXCpm0

db.collection.aggregate([
  {
    $match: {
      userId: {
        $in: [
          "6288ceea279219e36338b5bc"
        ]
      }
    }
  },
  {
    $sort: {
      _id: -1
    }
  },
  {
    $group: {
      _id: {
        userId: "$userId"
      },
      docs: {
        $first: "$$ROOT"
      }
    }
  },
  {
    $project: {
      _id: 0
    }
  },
  {
    $replaceRoot: {
      newRoot: "$docs"
    }
  }
])

CodePudding user response：

Follow these pipelines:

1. Group records with userId

   {
     $group:{
       _id:$userId,
       total:{$sum:1},
       movements:{
         $push:{
           _id:$_id,
           ...
         }
       }
     }
   } 
   

   output:

   [
   { 
      _id:1, //user id
      totalMovements:2 
      movements:[
          {
            _id:1,
            ...
          },
          {
            _id:2,
            ...
          }
      ] 
   },
   ...]
   

2. By default mongo return docs in ascending order. In group pipeline movements stored in ascending order. To Access lastest movement we need to access last element of array.

   {
      $project:{
          userId:$_id,
          movement:{
            $arrayElemAt:[
               $movements,
               { $subtract:[$count,1] }
            ]
          }  
      }
   }
   

   output:

   [
    {
       userId:1,
       movement:{   //latest movement
          _id:2,
          ...
       }  
     },
     ...
   ]