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: [
        { ... }, { ... }, { ... }
      ]
    }
  ]
}

这是我到目前为止所拥有的:

This is what I have so far:

// 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控制台中返回以下错误: The field 'page' must be an accumulator object

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屋！