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.

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});

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).

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

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

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

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?

回答1:

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 } } },
        ],
      },
    },
  ])

回答2:

Did this in two steps instead of one:

// Get the totals
db.mongoAuditEvent.aggregate([{$group: {_id: "$corrId"}}, {$group: {_id: 1, total: {$sum: 1}}}]);

// Get the data
db.mongoAuditEvent.aggregate([
  {$group: {
    _id : "$corrId", 
    currentEvent: {"$last": "$event.status"}, 
    "events": { $push: "$$ROOT"}
  }},
  {$sort: {"events.timestamp": -1} }, // Latest first
  {$skip: 0 },
  {$limit: 10}
], {allowDiskUse: true}).pretty();

I would be very happy if anybody got a better solution to this though.