How to group and get doc with max date for each unique value in MongoDB

user_5097224 0
Viewed 981

Sorry for the bad title. I didn't know how to word it. So, I have a set of data about states and I cannot figure out how to structure my pipeline to get the specific data. Here is an example of the documents I have in the collection.

{
    state: "California",
    date: "2010-08-11"
    otherData: "Something Else"
}

All documents are formatted in that way. What I want to do is to get one document for each state with the most recent date. I have tried using $group, but I can't figure out how to get the data from just one document. I tried using $match but I then I don't know how to get just one for each state.

aggregation-framework mongodb-query mongodb
2 Answers

You can try below query :

db.collection.aggregate([
    /** Group on state and get max doc for each state and max is based on `date` field */
    {
      $group: {
        _id: "$state",
        doc: { $max: { date: "$date", state: "$state", otherData: "$otherData" } } // max works on first field in object
      }
    },
    /** Replace `doc` field as new root */
    {
      $replaceRoot: { newRoot: "$doc" }
    }
  ])

Test : mongoplayground

user_72376130

Try the below query:

db.collection.aggregate([
  {
    $project: {
      state: 1,
      date: {
        $dateFromString: {
          dateString: "$date",
          format: "%Y-%m-%d"
        }
      }
    }
  },
  {
    $group: {
      _id: "$state",
      mostRecentDate: {
        $max: "$date"
      }
    }
  },
  {
    $project: {
      state: "$_id",
      date: {
        $dateToString: {
          date: "$mostRecentDate",
          format: "%Y-%m-%d"
        }
      },
      _id: 0
    }
  }
])

MongoPlayGroundLink

Assumption: Given dates are in YYYY-MM-DD format.

user_66354640
Related