Question 117867 in Mongodb Question posted in
The official documentation can be found here.

MongoDB: Aggregate by last inserted row by userId

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!

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    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"
    }
  }
])

  2. 0

    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,
       ...
    }  
  },
  ...
]
    Login or Signup to reply.
  3. 0

    I think this should work.

    .aggregate([
{
    $group: {
      _id: { userId : "$userId" },
      lastMoveX: { $last: "$moveX" },
      lastMoveY: { $last: "$moveY" },
      lastMoveZ: { $last: "$moveZ" }
    }
}]);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search