CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
DennyHiu
DennyHiu

Reputation: 6060

Limiting Query result in MongoDB

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

Upvotes: 7

Views: 11457

Answers (2)

Sede
Sede

Reputation: 61225

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

Upvotes: 5

Martins Untals
Martins Untals

Reputation: 2288

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

Upvotes: 10

Related Questions