Question 407756 in Javascript Question posted in
A very good W3school tutorial can be found here.

Javascript – How to select the last record for each record type in a MongoDB with typescript and mongoose

I’ve got a mongoDB collection that looks like:

[
 {user: "joe",  event: "foo", timestamp: 1},
 {user: "meg",  event: "blah", timestamp: 2},
 {user: "rick", event: "bork", timestamp: 3},
 {user: "joe",  event: "bing", timestamp: 4},
 {user: "meg",  event: "biz",  timestamp: 5},
 {user: "tim",  event: "boz",  timestamp: 6}
]

and I want to find the latest event for each user, so get a set of results like:

[
 {user: "rick", event: "bork", timestamp: 3},
 {user: "joe",  event: "bing", timestamp: 4},
 {user: "meg",  event: "biz",  timestamp: 5},
 {user: "tim",  event: "boz",  timestamp: 6}
]

I’ve looked over the mongoose aggregation framework docs, and it seems like there should be a way to do this simply, but none of the examples I’ve seen do quite exactly this. Lots of them group by user, but then the resulting groupings lose some of the data, and I’m not sure how to reconstitute the full documents at the end.

Tags:

2

Answers


  1. 0

    1. $setWindowFields – Partition/Group the document by the user field, order each partition by timestamp descending, and add the rank field with the $rank operator.

    2. $match – Filter the document with rank: 1 indicates getting the document with the latest timestamp for each partition.

    3. $sort – Order the document by timestamp ascending.

    4. $unset – Remove the rank field from the documents.

    db.collection.aggregate([
  {
    $setWindowFields: {
      partitionBy: "$user",
      sortBy: {
        timestamp: -1
      },
      output: {
        rank: {
          $rank: {}
        }
      }
    }
  },
  {
    $match: {
      rank: 1
    }
  },
  {
    $sort: {
      timestamp: 1
    }
  },
  {
    $unset: "rank"
  }
])

    Demo @ Mongo Playground

    Login or Signup to reply.
  2. 0

    you can use a combination of sort group and project, i have solved for you

    db.collection.aggregate([
  { $sort: { timestamp: -1 } },

  {
    $group: {
      _id: '$user',
      doc: { $first: '$$ROOT' },
    },
  },
  {
    $project: {
      _id: 0,
      user: '$doc.user',
      event: '$doc.event',
      timestamp: '$doc.timestamp',
    },
  },
])

    in this way you can solve without losing any document info

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search