Efficiently find the most recent filtered document in MongoDB collection using datetime field-CodePudding

I have a large collection of documents with datetime fields in them, and I need to retrieve the most recent document for any given queried list.

Sample data:

[
  {"_id": "42.abc",
   "ts_utc": "2019-05-27T23:43:16.963Z"},
  {"_id": "42.def",
   "ts_utc": "2019-05-27T23:43:17.055Z"},
  {"_id": "69.abc",
   "ts_utc": "2019-05-27T23:43:17.147Z"},
  {"_id": "69.def",
   "ts_utc": "2019-05-27T23:44:02.427Z"}
]

Essentially, I need to get the most recent record for the "42" group as well as the most recent record for the "69" group. Using the sample data above, the desired result for the "42" group would be document "42.def".

My current solution is to query each group one at a time (looping with PyMongo), sort by the ts_utc field, and limit it to one, but this is really slow.

// Requires official MongoShell 3.6 
db = db.getSiblingDB("someDB");
db.getCollection("collectionName").find(
    { 
        "_id" : /^42\..*/
    }
).sort(
    { 
        "ts_utc" : -1.0
    }
).limit(1);

Is there a faster way to get the results I'm after?

CodePudding user response：

Assuming all your documents are have the format displayed above, you can split the id into two parts (using the dot character) and use aggregation to find the max element per each first array (numeric) element.

That way you can do it in a one shot, instead of iterating per each group.

db.foo.aggregate([
    { $project: { id_parts : { $split: ["$_id", "."] }, ts_utc : 1 }},
    { $group: {"_id" : { $arrayElemAt: [ "$id_parts", 0 ] }, max : {$max: "$ts_utc"}}}
])

CodePudding user response：

As @danh mentioned in the comment, the best way you can do is probably adding an auxiliary field to indicate the grouping. You may further index the auxiliary field to boost the performance.

Here is an ad-hoc way to derive the field and get the latest result per grouping:

db.collection.aggregate([
  {
    "$addFields": {
      "group": {
        "$arrayElemAt": [
          {
            "$split": [
              "$_id",
              "."
            ]
          },
          0
        ]
      }
    }
  },
  {
    $sort: {
      ts_utc: -1
    }
  },
  {
    "$group": {
      "_id": "$group",
      "doc": {
        "$first": "$$ROOT"
      }
    }
  },
  {
    "$replaceRoot": {
      "newRoot": "$doc"
    }
  }
])

Here is the Mongo playground for your reference.