主页 / dba / 问题 / 342335
Accepted
SqlDBA
Asked: 2024-09-13 08:08:06 +0800 CST

根据创建时间或证书编号字段获取每个证书编号的最新记录

  • 772

我正在尝试复制每个证书编号值的最新记录。我们每个证书都有多个文档，并尝试获取每个证书的最新记录并将其复制到新集合中。

数据如下：

{ "_id": ..., "certnumber":"123456", "createtime": "2024-02-03T16:42:20.324Z", "company": "A" ,.....}
{ "_id": ..., "certnumber":"123456", "createtime": "2024-02-03T16:32:20.324Z", "company": "A" ,.....}
{ "_id": ..., "certnumber":"123456", "createtime": "2024-02-02T14:28:20.324Z", "company": "A" ,.....}
{ "_id": ..., "certnumber":"123456", "createtime": "2024-02-02T13:22:20.324Z", "company": "A" ,.....}
{ "_id": ..., "certnumber":"654321", "createtime": "2024-02-05T17:42:20.324Z", "company": "B" ,.....}
{ "_id": ..., "certnumber":"654321", "createtime": "2024-02-05T16:42:20.324Z", "company": "B" ,.....}
{ "_id": ..., "certnumber":"654321", "createtime": "2024-02-03T15:32:20.324Z", "company": "B" ,.....}
{ "_id": ..., "certnumber":"654321", "createtime": "2024-02-03T14:22:20.324Z", "company": "B" ,.....}

我尝试使用以下命令收集最新记录，它有效，但只返回 2 个字段。如何提取整个文档而不是仅提取 2 个字段？

db.collectionname.aggregate(
   [
     { $sort: { certnumber: 1, createtime: 1} },
     {
       $group:
         {
           _id: {certnumber : "$certnumber" },
           updatedDate: { $last: "$createtime" }
         }
     },
   ],
)
mongodb

1 个回答

  1. Best Answer

    有两种方法可以实现这一点。

    1. 使用$setWindowFields来计算内部的排名certnumber并仅选择排名：1 的文档到新集合中。
    db.collection.aggregate([
  {
    "$setWindowFields": {
      "partitionBy": "$certnumber",
      "sortBy": {
        "createtime": -1
      },
      "output": {
        "rank": {
          "$rank": {}
        }
      }
    }
  },
  {
    "$match": {
      "rank": 1
    }
  },
  {
    "$unset": "rank"
  },
  {
    "$merge": {
      "into": "collection2"
    }
  }
])

    蒙戈游乐场

    1. 与您当前的方法类似，$sort然后选择$first/$last $$ROOT文档。
    db.collection.aggregate([
  {
    "$sort": {
      "certnumber": 1,
      "createtime": 1
    }
  },
  {
    "$group": {
      "_id": "$certnumber",
      "lastDoc": {
        "$last": "$$ROOT"
      }
    }
  },
  {
    "$replaceRoot": {
      "newRoot": "$lastDoc"
    }
  },
  {
    "$merge": {
      "into": "collection2"
    }
  }
])

    蒙戈游乐场

    • 1

相关问题