mongodb - 查找mongoDB中特定字段的最新数据

Question

我在 MongoDB 集合中设置了以下数据。我需要根据字段“eventType”查找最新数据。

{
        "_id" : ObjectId("5d5690843248b8c20481f5e9"),
        "mrn" : "xp35",
        "eventType" : "LAB",
        "eventSubType" : "CBC",
        "value" : 1,
        "units" : 1,
        "charttime" : ISODate("2019-08-16T16:46:21.393Z")
}
{
        "_id" : ObjectId("5d5690843248b8c20481f5e9"),
        "mrn" : "xp35",
        "eventType" : "LAB",
        "eventSubType" : "CBB",
        "value" : 1,
        "units" : 1,
        "charttime" : ISODate("2019-08-16T16:46:22.393Z")
}
{
        "_id" : ObjectId("5d5690843248b8c20481f5ea"),
        "mrn" : "zfwy",
        "eventType" : "EDLIST",
        "eventSubType" : "Lipids",
        "value" : 1,
        "units" : 1,
        "charttime" : ISODate("2019-08-16T16:46:23.394Z")
}
{
        "_id" : ObjectId("5d5690843248b8c20481f5ea"),
        "mrn" : "zfwy",
        "eventType" : "EDLIST",
        "eventSubType" : "L",
        "value" : 1,
        "units" : 1,
        "charttime" : ISODate("2019-08-16T16:46:24.394Z")
}

我使用“聚合”和“查找”查询并根据时间戳字段“charttime”对其进行排序以获取最新数据，但它不起作用。我需要根据字段“eventType”获取数据，以便对于每个“eventType”我应该获取最新数据。因此，在给定的示例中，我应该获取“LAB”和“EDLIST”的最新数据。理想情况下，它应该返回数据:

{
        "_id" : ObjectId("5d5690843248b8c20481f5e9"),
        "mrn" : "xp35",
        "eventType" : "LAB",
        "eventSubType" : "CBB",
        "value" : 1,
        "units" : 1,
        "charttime" : ISODate("2019-08-16T16:46:22.393Z")
}
{
        "_id" : ObjectId("5d5690843248b8c20481f5ea"),
        "mrn" : "zfwy",
        "eventType" : "EDLIST",
        "eventSubType" : "L",
        "value" : 1,
        "units" : 1,
        "charttime" : ISODate("2019-08-16T16:46:24.394Z")
}

Answer 1

请按照以下步骤操作:

• 首先对所有文档进行排序。
• 按事件类型对其进行分组。
• 再次项目以将 id 正确地获取到 _id (如果您同意 id 键，则不需要)
• 重新对这些数据进行排序(如果您同意不按日期排序的不同eventype，则没有必要)

db.collection.aggregate([
  { $sort: {"charttime": 1 }},
  { $group: {
      _id: "$eventType",
      id: {$first: "$_id"},
      "mrn": {$first: "$mrn"},
      "eventType": {$first: "$eventType"},
      "eventSubType": {$first: "$eventSubType"},
      "value": {$first: "$value"},
      "units": {$first: "$units"},
      "charttime": {$first: "$charttime"}
  }},
  {$project: {
      _id: "$id",
      "mrn": 1,
      "eventType": 1,
      "eventSubType": 1,
      "value": 1,
      "units": 1,
      "charttime": 1
  }},
  { $sort: {"charttime": 1 }}
])

希望这有帮助!

输出:

/* 1 */
{
    "_id" : ObjectId("5d5cedb1fc18699f18a24fa2"),
    "mrn" : "xp35",
    "eventType" : "LAB",
    "eventSubType" : "CBB",
    "value" : 1,
    "units" : 1,
    "charttime" : ISODate("2019-08-16T16:46:22.393Z")
}

/* 2 */
{
    "_id" : ObjectId("5d5cedc1fc18699f18a24fa9"),
    "mrn" : "zfwy",
    "eventType" : "EDLIST",
    "eventSubType" : "L",
    "value" : 1,
    "units" : 1,
    "charttime" : ISODate("2019-08-16T16:46:24.394Z")
}

=====更新=====

根据您的要求优化查询:

db.collection.aggregate([
  { $sort: {"charttime": -1 }}, // Sort in descending. (So we would not have another sort after group)
  { $group: { 
      _id: "$eventType", // Group by event type
      data: {$first: "$$ROOT"} // Take whole first record
  }},
  { $replaceRoot: { newRoot: "$data" }} // Replaceroot to have document as per your requirement
])

====更新2 ====

对于太多记录: - 查找事件类型和最大图表时间 - 迭代每个文档并获取记录(您可能会对数据库进行多次调用，但会花费更少的时间)

db.getCollection('Vehicle').aggregate([
  { $group: { 
      _id: "$eventType", // Group by event type
      maxChartTime: {$max: "$charttime"}
  }}
]).forEach(function(data) {
    db.getCollection('Vehicle').find({
      "eventType": data._id,
      "charttime": data.maxChartTime
    });
    // Any mechanism to have array of all retrieved documents.
    // You can handle it from your back end too.
})

Note:- I have tested it with 506983 records and got results in 0.526 sec.