具有分页数据和总计的 Mongo 聚合 [英] Mongo aggregation with paginated data and totals

问题描述

我已经爬遍了堆栈溢出，但没有找到任何关于如何返回结果集中包含的正确分页数据的信息.

I've crawled all over stack overflow, and have not found any info on how to return proper pagination data included in the resultset.

我正在尝试从我的 mongo 商店汇总一些数据.我想要的是有回报:

I'm trying to aggregate some data from my mongo store. What I want, is to have something return:

{
  total: 5320,
  page: 0,
  pageSize: 10,
  data: [
    {
      _id: 234,
      currentEvent: "UPSTREAM_QUEUE",
      events: [
        { ... }, { ... }, { ... }
      ]
    },
    {
      _id: 235,
      currentEvent: "UPSTREAM_QUEUE",
      events: [
        { ... }, { ... }, { ... }
      ]
    }
  ]
}

这是我目前所拥有的:

// page and pageSize are variables
db.mongoAuditEvent.aggregate([
  // Actual grouped data
  {"$group": {
    "_id" : "$corrId", 
    "currentEvent": {"$last": "$event.status"}, 
    "events": { $push: "$$ROOT"}
  }},
  // Pagination group
  {"$group": {
    "_id": 0,
    "total": { "$sum": "corrId" },
    "page": page,
    "pageSize": pageSize,
    "data": {
      "$push": {
        "_id": "$_id",
        "currentEvent": "$currentEvent",
        "events": "$events"
      }
    }
  }},
  {"$sort": {"events.timestamp": -1} }, // Latest first
  {"$skip": page },
  {"$limit": pageSize }
], {allowDiskUse: true});

我正在尝试将分页组作为根，其中包含实际的分组数据(这样我就可以得到实际的总数，同时仍然保留 skip 和 limits).

I'm trying to have a pagination group as root, containing the actual grouped data inside (so that I get actual totals, whilst still retaining skip and limits).

以上代码会在 mongo 控制台返回如下错误:字段'page'必须是一个累加器对象

The above code will return the following error in mongo console: The field 'page' must be an accumulator object

如果我从分页组中删除 page 和 pageSize，我仍然会收到以下错误:

If I remove the page and pageSize from the pagination group, I still get the following error:

BSONObj 大小:45707184 (0x2B96FB0) 无效.大小必须介于 0 和 16793600(16MB) 之间 第一个元素:id:0

BSONObj size: 45707184 (0x2B96FB0) is invalid. Size must be between 0 and 16793600(16MB) First element: id: 0

如果我一起删除分页组，查询工作正常.但我确实需要返回我存储了多少个文档 total，虽然实际上没有必要，但 page 和 pageSize 会很好地返回为好吧.

If I remove the pagination group alltogether, the query works fine. But I really need to return how many documents I have stored total, and allthough not actually necessary, page and pageSize would be nice to return as well.

谁能告诉我我做错了什么?或者告诉我是否有可能一次性做到这一点?

Can somebody please tell me what I am doing wrong? Or tell me if it is at all possible to do this in one go?

推荐答案

如果你有很多事件，{$ push: "$$ ROOT"}，会让Mongo返回错误，我已经用$facet(仅适用于 3.4+ 版本)

If you have a lot of events, {$ push: "$$ ROOT"}, will make Mongo return an error, I have solved it with $facet (Only works with version 3.4+)

aggregate([
    { $match: options },
    {
      $facet: {
        edges: [
          { $sort: sort },
          { $skip: skip },
          { $limit: limit },
        ],
        pageInfo: [
          { $group: { _id: null, count: { $sum: 1 } } },
        ],
      },
    },
  ])

这篇关于具有分页数据和总计的 Mongo 聚合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！