根据MongoDB中的字段获取最后的记录

发布于 2025-01-23 23:18:05 字数 1169 浏览 0 评论 0 原文

我是Mongo的学习者和新手。我正在尝试根据特定字段获取最后的记录。在SQL 中，按DESC限制1 从PowerAndlevel组中选择 *。因此，在基于DeviceID的SQL查询中，我可以获取每个DeviceID的最后一个记录。我想在mongodb中做同样的事情

[{
_id: "ObjectId(6246ab45e95eac6c85726cfc)"
imageName: "IMG_123456.jpg"
eventTime: "2022-04-01T07:35:30.615Z"
deviceId: "48701ED21819"
},
{
_id: "ObjectId(6246ab45e95eac6c85726cfc)"
imageName: "IMG_123456.jpg"
eventTime: "2022-04-01T07:38:10.543Z"
deviceId: "58701ED21819"
},
{
_id: "ObjectId(6246ab45e95eac6c85726cfc)"
imageName: "IMG_123456.jpg"
eventTime: "2022-04-01T08:05:50.865Z"
deviceId: "48701ED21819"
}]

，并且我正在使用此查询 db.imageevent.aggregate（[{{“ $ group”：{“ _id”：{“ deviceId”：“ $ decep但：{eventtime：1}}，{$ limit：1}]）

结果

[{ "_id" : { "deviceId" : "58701ED21819" } },
{ "_id" : { "deviceId" : "48701ED21819" } }]

，并期望这样的结果

[{
_id: "ObjectId(6246ab45e95eac6c85726cfc)"
imageName: "IMG_123456.jpg"
eventTime: "2022-04-01T07:38:10.543Z"
deviceId: "58701ED21819"
},
{
_id: "ObjectId(6246ab45e95eac6c85726cfc)"
imageName: "IMG_123456.jpg"
eventTime: "2022-04-01T08:05:50.865Z"
deviceId: "48701ED21819"
}]

原文

I'm a learner and new at mongo. I'm trying to fetch the last records according to particular field. In SQL select * from powerandlevel group by deviceId order by desc limit 1. So in this SQL query based on deviceId I can get the last record of every deviceId. Same I want do in mongodb

[{
_id: "ObjectId(6246ab45e95eac6c85726cfc)"
imageName: "IMG_123456.jpg"
eventTime: "2022-04-01T07:35:30.615Z"
deviceId: "48701ED21819"
},
{
_id: "ObjectId(6246ab45e95eac6c85726cfc)"
imageName: "IMG_123456.jpg"
eventTime: "2022-04-01T07:38:10.543Z"
deviceId: "58701ED21819"
},
{
_id: "ObjectId(6246ab45e95eac6c85726cfc)"
imageName: "IMG_123456.jpg"
eventTime: "2022-04-01T08:05:50.865Z"
deviceId: "48701ED21819"
}]

And I'm using this query db.imageEvent.aggregate([{ "$group" : {"_id" : { "deviceId" : "$deviceId"}}}, {$sort: {eventTime: 1}}, { $limit : 1 }])

Result

[{ "_id" : { "deviceId" : "58701ED21819" } },
{ "_id" : { "deviceId" : "48701ED21819" } }]

And expecting a result like this

[{
_id: "ObjectId(6246ab45e95eac6c85726cfc)"
imageName: "IMG_123456.jpg"
eventTime: "2022-04-01T07:38:10.543Z"
deviceId: "58701ED21819"
},
{
_id: "ObjectId(6246ab45e95eac6c85726cfc)"
imageName: "IMG_123456.jpg"
eventTime: "2022-04-01T08:05:50.865Z"
deviceId: "48701ED21819"
}]

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

淡水深流 2025-01-30 23:18:05

如果您不需要每个结果的 objectID ，只是公共值和最新日期，您可以通过：

db.imageEvent.aggregate([
  {
    $group: {
      _id: "$deviceId",
      imageName: {$first: "$imageName"},
      eventTime: {$max: "$eventTime"},
      deviceId: {$first: "$deviceId"},
    }
  }
])

如下：如playground
$ first 对于组相同的值很方便。 $ max 将为您提供最新日期。

这将阻止您对整个集合进行排序，并在查询期间创建大型文档，因为文档的上限大小。

If you don't need the ObjectId of each result, just the common values and the latest date, you can simplify it by:

db.imageEvent.aggregate([
  {
    $group: {
      _id: "$deviceId",
      imageName: {$first: "$imageName"},
      eventTime: {$max: "$eventTime"},
      deviceId: {$first: "$deviceId"},
    }
  }
])

As you can see on the playground
The $first is convenient for values that are the same for the group. The $max will give you the latest date.

This will prevent you from sorting the entire collection and from creating large documents during the query, since there is a cap size for a document.

回复收藏 0 原文

留蓝 2025-01-30 23:18:05

$ sort - 通过 EventTime desc进行排序。
$ group - 组成的 deviceId 组，然后将第一个文档通过 data 通过 $ first 字段。
$替换 - 用 data 替换输入文档。

db.imageEvent.aggregate([
  {
    $sort: {
      eventTime: -1
    }
  },
  {
    "$group": {
      "_id": {
        "deviceId": "$deviceId"
      },
      data: {
        $first: "$ROOT"
      }
    }
  },
  {
    "$replaceRoot": {
      "newRoot": "$data"
    }
  }
])

示例mongo playground

$sort - Sort by eventTime DESC.
$group - Group by deviceId and take first document into data field via $first.
$replaceRoot - Replace the input document with data.

db.imageEvent.aggregate([
  {
    $sort: {
      eventTime: -1
    }
  },
  {
    "$group": {
      "_id": {
        "deviceId": "$deviceId"
      },
      data: {
        $first: "$ROOT"
      }
    }
  },
  {
    "$replaceRoot": {
      "newRoot": "$data"
    }
  }
])

Sample Mongo Playground

回复收藏 0 原文

~没有更多了~