Get the latest record for each named one

Given a table with multiple records of different dates, something like

+------+------+---------------------+
| id   | name | updated             |
+------+------+---------------------+
|  100 | a    | 2020-08-20 10:33:47 |
|  101 | b    | 2020-08-20 10:34:05 |
|  102 | a    | 2020-08-20 10:34:34 |
|  103 | b    | 2020-08-20 10:34:34 |
|  104 | a    | 2020-08-20 10:42:10 |
|  105 | a    | 2020-08-20 10:42:36 |
+------+------+---------------------+

I want to retrieve the latest of each named record, in sql:

select t1.* from test t1 join (select name, max(updated) as updated from test group by name) t2 on t1.name = t2.name and t1.updated = t2.updated;

+------+------+---------------------+
| id   | name | updated             |
+------+------+---------------------+
|  103 | b    | 2020-08-20 10:34:34 |
|  105 | a    | 2020-08-20 10:42:36 |
+------+------+---------------------+

How do I do that in Mongodb?

Hello ,welcome : )

MongoDB provides the aggregation operator $max.
This means that you can group,and always keep the $max or $min value of each group.

Comparisons works in documents also,so we can compare documents.

To have the document with the latest date first,it must be the first field of the document,
to compare the dates first.

The first one is more complicated with mergeObject you avoid to hand type all attributes
you say all attributes.

{
  "aggregate": "testcoll",
  "pipeline": [
    {
      "$group": {
        "_id": "$name",
        "latestDate": {
          "$max": {
            "$mergeObjects": [
              {
                "updated": "$updated"
              },
              "$$ROOT"
            ]
          }
        }
      }
    },
    {
      "$addFields": {
        "name": "$_id"
      }
    },
    {
      "$project": {
        "_id": 0
      }
    },
    {
      "$unwind": {
        "path": "$latestDate"
      }
    },
    {
      "$replaceRoot": {
        "newRoot": "$latestDate"
      }
    }
  ],
  "maxTimeMS": 0,
  "cursor": {}
}

If you are new and $$ROOT unwind replaceRoot mergeObjects etc look complicated.
This works also,but you type the fields by hand,in group and in project.

{
  "aggregate": "testcoll",
  "pipeline": [
    {
      "$group": {
        "_id": "$name",
        "latestDate": {
          "$max": {
            "updated": "$updated",
            "_id": "$_id"
          }
        }
      }
    },
    {
      "$addFields": {
        "name": "$_id"
      }
    },
    {
      "$project": {
        "_id": 0
      }
    },
    {
      "$project": {
        "_id": "$latestDate._id",
        "name": 1,
        "updated": "$latestDate.updated"
      }
    }
  ],
  "maxTimeMS": 0,
  "cursor": {}
}

Take the pipeline and run it to see if it is what you want i test it and got your results,also the $addFields $project in the middle,does only a rename of the field.

Hope it helps.

2 Likes

As a general comment, in the future, please provide your sample documents and desired output as JSON document. This way, people willing to help, will have an easier time to enter your data into their environment in order to be able to experiment and provide tested solution. You might even have more people will to help.

Can you please provide an equivalent query using lucid-mongodb ?

Welcome to the MongoDB Community Forums @daitha_ranjith !

Please start a new discussion with details of your question such as:

  • example documents
  • desired output
  • version of MongoDB driver & server
  • a code snippet for what you’ve tried
  • any error messages received

Thanks,
Stennie