Mongo aggregation with paginated data and totals

Question

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?

What do you mean by *it will not work*? Error? Unexpected results? Which part of query fails? — Sergey Berezovskiy, Feb 08 '17 at 11:07

score 45 · Accepted Answer · edited Sep 26 '18 at 14:47

45

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

edited Sep 26 '18 at 14:47

Vaishak

462
2
6
17

answered Oct 26 '17 at 10:12

migue diaz

501
5
7

1

why are you setting `_id: null`? Great answer tho, thank you – insivika Oct 02 '20 at 03:23
Why is $count not used? It's also 3.4+. Are there performance issues? – T Tse Oct 15 '20 at 10:00
@insivika _id: null aggroup all match, you can use _id: "any string" too. – migue diaz Feb 18 '21 at 14:58
This won't work if your match returns 10 results and your skip is 100. If I am on page 2 of a list and I search for something, you will have to reset the skip to 0. Just a quick thing I saw, could be what you want – Stefan Sep 03 '21 at 22:03

Amin Shojaei · Answer 2 · 2020-11-30T06:40:51.057

5

A performance optimization tip:

When you use $facet stage for pagination, Try to add it as soon as it's possible.

For example: if you want to add $project or $lookup stage, add them after $facet, not before it.

it will have impressive effect in aggregation speed. because $project stage require MongoDB to explore all documents and get involve with all fields(which is not necessary).

edited Nov 30 '20 at 06:40

answered Dec 09 '19 at 10:56

Amin Shojaei

5,451
2
38
46

score 3 · Answer 3 · answered Feb 08 '17 at 12:17

3

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.

answered Feb 08 '17 at 12:17

Øystein Amundsen

3,993
8
44
63

Doesn't this break past 2.4 mongo? https://docs.mongodb.com/manual/reference/operator/aggregation/limit/ – Petrogad Jan 30 '18 at 12:29
This issue with this kind of group is that you may well end up building too large of a document (> 16Gb) – Olivier D. Nov 18 '21 at 10:20

Mongo aggregation with paginated data and totals

3 Answers3

Linked