CODEWITHSUNDEEP
mongodbcountlimit
Geoff
Geoff

Reputation: 9570

Limiting results in MongoDB but still getting the full count?

For speed, I'd like to limit a query to 10 results

db.collection.find( ... ).limit(10)

However, I'd also like to know the total count, so to say "there were 124 but I only have 10". Is there a good efficient way to do this?

Upvotes: 46

Views: 30342

Answers (6)

Xavier Guihot
Xavier Guihot

Reputation: 61666

You can use a $facet stage which processes multiple aggregation pipelines within a single stage on the same set of input documents:

// { item: "a" }
// { item: "b" }
// { item: "c" }
db.collection.aggregate([
  { $facet: {
    limit: [{ $limit: 2 }],
    total: [{ $count: "count" }]
  }},
  { $set: { total: { $first: "$total.count" } } }
])
// { limit: [{ item: "a" }, { item: "b" }], total: 3 }

This way, within the same query, you can get both some documents (limit: [{ $limit: 2 }]) and the total count of documents ({ $count: "count" }).

The final $set stage is an optional clean-up step, just there to project the result of the $count stage, such that "total" : [ { "count" : 3 } ] becomes total: 3.

Upvotes: 0

Filip Voska
Filip Voska

Reputation: 321

There is a solution using push and slice: https://stackoverflow.com/a/39784851/4752635

I prefe

  1. First for filtering and then grouping by ID to get number of filtered elements. Do not filter here, it is unnecessary.
  2. Second query which filters, sorts and paginates.

Solution with pushing $$ROOT and using $slice runs into document memory limitation of 16MB for large collections. Also, for large collections two queries together seem to run faster than the one with $$ROOT pushing. You can run them in parallel as well, so you are limited only by the slower of the two queries (probably the one which sorts).

I have settled with this solution using 2 queries and aggregation framework (note - I use node.js in this example, but idea is the same):

var aggregation = [
  {
    // If you can match fields at the begining, match as many as early as possible.
    $match: {...}
  },
  {
    // Projection.
    $project: {...}
  },
  {
    // Some things you can match only after projection or grouping, so do it now.
    $match: {...}
  }
];


// Copy filtering elements from the pipeline - this is the same for both counting number of fileter elements and for pagination queries.
var aggregationPaginated = aggregation.slice(0);

// Count filtered elements.
aggregation.push(
  {
    $group: {
      _id: null,
      count: { $sum: 1 }
    }
  }
);

// Sort in pagination query.
aggregationPaginated.push(
  {
    $sort: sorting
  }
);

// Paginate.
aggregationPaginated.push(
  {
    $limit: skip + length
  },
  {
    $skip: skip
  }
);

// I use mongoose.

// Get total count.
model.count(function(errCount, totalCount) {
  // Count filtered.
  model.aggregate(aggregation)
  .allowDiskUse(true)
  .exec(
  function(errFind, documents) {
    if (errFind) {
      // Errors.
      res.status(503);
      return res.json({
        'success': false,
        'response': 'err_counting'
      });
    }
    else {
      // Number of filtered elements.
      var numFiltered = documents[0].count;

      // Filter, sort and pagiante.
      model.request.aggregate(aggregationPaginated)
      .allowDiskUse(true)
      .exec(
        function(errFindP, documentsP) {
          if (errFindP) {
            // Errors.
            res.status(503);
            return res.json({
              'success': false,
              'response': 'err_pagination'
            });
          }
          else {
            return res.json({
              'success': true,
              'recordsTotal': totalCount,
              'recordsFiltered': numFiltered,
              'response': documentsP
            });
          }
      });
    }
  });
});

Upvotes: -1

Vibhu Tewary
Vibhu Tewary

Reputation: 292

The accepted answer by @johnnycrab is for the mongo CLI.

If you have to write the same code in Node.js and Express.js, you will have to use it like this to be able to use the "count" function along with the toArray's "result".

var curFind = db.collection('tasks').find({query});

Then you can run two functions after it like this (one nested in the other)

curFind.count(function (e, count) {

// Use count here

    curFind.skip(0).limit(10).toArray(function(err, result) {

    // Use result here and count here

    });

});

Upvotes: 6

zeedre
zeedre

Reputation: 411

Doing count(1) includes limit and skip.

Upvotes: 27

johnnycrab
johnnycrab

Reputation: 997

By default, count() ignores limit() and counts the results in the entire query. So when you for example do this, var a = db.collection.find(...).limit(10); running a.count() will give you the total count of your query.

Upvotes: 49

nutlike
nutlike

Reputation: 4975

cursor.count() should ignore cursor.skip() and cursor.limit() by default.

Source: http://docs.mongodb.org/manual/reference/method/cursor.count/#cursor.count

Upvotes: 4

Related Questions