Limiting Query result in MongoDB

Question

I have 20,000+ documents in my mongodb. I just learnt that you cannot query them all in one go.

So my question is this:

I want to get my document using find(query) then limit its results for 3 documents only and I can choose where those documents start from.

For example if my find() query resulted in 8 documents :

[{doc1}, {doc2}, {doc3}, {doc4}, {doc5}, {doc6}, {doc7}, {doc 8}]

command limit(2, 3) will gives [doc3, doc4, doc5]

And I also need to get total count for all that result(without limit) for example : length() will give 8 (the number of total document resulted from find() function)

Any suggestion? Thanks

score 10 · Answer 1 · answered Dec 27 '15 at 16:02

10

add .skip(2).limit(3) to the end of your query

answered Dec 27 '15 at 16:02

Martins Untals

2,128
2
18
31

Ah, thank you for such quick answer. So how I get the total result ? I mean, I want to know how many document count form find() without querying the same search parameter twice ? – DennyHiu Dec 27 '15 at 16:11
I am not sure what do you mean. you want to count how many results did you get by limiting result set to 3? – Martins Untals Dec 27 '15 at 16:32
No, I building a datatable (jquery datatable) and it requires something like - var total = db.transaction.find().length; and var data = db.transaction.find().skip(2).limit(3). TOTAL and DATA are different thing – DennyHiu Dec 27 '15 at 16:35
total count is gtiven by .count() at end of find() function – Martins Untals Dec 27 '15 at 16:43
Yes, your answer is also correct and I even write the same code with count() function. But it's inefficient since I need to query such a big data twice : first to get its total count and second, just to split it. – DennyHiu Dec 27 '15 at 16:53
5

well, then you should have stated that in the question in the first place. I think I answered exactly what you asked. answering question about how to skip and limit find query in mongo with description of aggregation pipeline would have been an overkill. – Martins Untals Dec 27 '15 at 17:01
@MartinsUntals the problem was you not trying to ask clarification and trying to answer a question that was "too broad", easy or that could be closed as a possible duplicate of [Mongoose limit/offset and count query](http://stackoverflow.com/questions/13935733/mongoose-limit-offset-and-count-query) – styvane Dec 27 '15 at 17:15
1

probably. though how can it be duplicate of anything related to mongoose, if this is pure mongo question? I can agree it being too easy though. Such questions should not be answered without asking extra questions first? - I am not sure, as I do not have extensive experience with writing answers yet. – Martins Untals Dec 27 '15 at 17:43
@MartinsUntals: My bad, I'm so sorry I forget to add detail about total earlier. Thank you for your answer though – DennyHiu Dec 28 '15 at 00:13

score 5 · Accepted Answer · edited May 23 '17 at 12:10

I suppose you have the following documents in your collection.

{ "_id" : ObjectId("56801243fb940e32f3221bc2"), "a" : 0 }
{ "_id" : ObjectId("56801243fb940e32f3221bc3"), "a" : 1 }
{ "_id" : ObjectId("56801243fb940e32f3221bc4"), "a" : 2 }
{ "_id" : ObjectId("56801243fb940e32f3221bc5"), "a" : 3 }
{ "_id" : ObjectId("56801243fb940e32f3221bc6"), "a" : 4 }
{ "_id" : ObjectId("56801243fb940e32f3221bc7"), "a" : 5 }
{ "_id" : ObjectId("56801243fb940e32f3221bc8"), "a" : 6 }
{ "_id" : ObjectId("56801243fb940e32f3221bc9"), "a" : 7 }

From MongoDB 3.2 you can use the .aggregate() method and the $slice operator.

db.collection.aggregate([
    { "$group": {
        "_id": null, 
        "count": { "$sum": 1 }, 
        "docs": { "$push": "$$ROOT" }
    }}, 
    { "$project": { 
        "count": 1, 
        "_id": 0,
        "docs": { "$slice": [ "$docs", 2, 3 ] }
    }}
])

Which returns:

{
        "count" : 8,
        "docs" : [
                {
                        "_id" : ObjectId("56801243fb940e32f3221bc4"),
                        "a" : 2
                },
                {
                        "_id" : ObjectId("56801243fb940e32f3221bc5"),
                        "a" : 3
                },
                {
                        "_id" : ObjectId("56801243fb940e32f3221bc6"),
                        "a" : 4
                }
        ]
}

You may want to sort your document before grouping using the $sort operator.

From MongoDB 3.0 backwards you will need to first $group your documents and use the $sum accumulator operator to return the "count" of documents; also in that same group stage you need to use the $push and the $$ROOT variable to return an array of all your documents. The next stage in the pipeline will then be the $unwind stage where you denormalize that array. From there use use the $skip and $limit operators respectively skip the first 2 documents and passes 3 documents to the next stage which is another $group stage.

db.collection.aggregate([
    { "$group": {
        "_id": null, 
        "count": { "$sum": 1 }, 
        "docs": { "$push": "$$ROOT" }
    }}, 
    { "$unwind": "$docs" }, 
    { "$skip": 2 }, 
    { "$limit": 3 }, 
    { "$group": {
        "_id": "$_id", 
        "count": { "$first": "$count" }, 
        "docs": { "$push": "$docs" }
    }}
])

As @JohnnyHK pointed out in this comment

$group is going to read all documents and build a 20k element array with them just to get three docs.

You should then run two queries using find()

db.collection.find().skip(2).limit(3)

and

db.collection.count()

Good ideas, but that `$group` is going to read all documents and build a 20k element array with them just to get three docs. Hard to believe this would be more efficient than two queries. — JohnnyHK, Dec 27 '15 at 17:14

Limiting Query result in MongoDB

2 Answers2