find and count in single mongodb query

Question

My documents looks like this.

{
"_id" : ObjectId("572c4bffd073dd581edae045"),
"name" : "What's New in PHP 7",
"description" : "PHP 7 is the first new major version number of PHP since 2004. This course shows what's new, and what's changed.",
"difficulty_level" : "Beginner",
"type" : "Normal",
"tagged_skills" : [ 
    {
        "_id" : "5714e894e09a0f7d804b2254",
        "name" : "PHP"
    }
],
"created_at" : 1462520831.649,
"updated_at" : 1468233074.243    }

Is it possible to get recent 5 documents and total count in a single query. I am using two queries for this requirement as given below.

db.course.find().sort({created_at:-1}).limit(5)
db.course.count()

Sede · Accepted Answer · 2016-08-04 11:13:23Z

7

This is a perfect job for the aggregation framework.

db.course.aggregate(
    [
        { "$sort": { "created_at": -1 }},
        { "$group": {
            "_id": null, 
            "docs": { "$push": "$$ROOT" }, 
            "count": { "$sum": 1 }
        }},
        { "$project": { "_id": 0, "count": 1, "docs": { "$slice": [ "$docs", 5 ] } }}
    ]
)

If your MongoDB server doesn't support $slice then you need to use the ugly and inefficient approach.

db.course.aggregate(
    [
        { "$sort": { "created_at": -1 }},
        { "$group": {
            "_id": null, 
            "docs": { "$push": "$$ROOT" }, 
            "count": { "$sum": 1 }
        }},
        { "$unwind": "$docs" },
        { "$limit": 5 }
    ]
)

answered Aug 4, 2016 at 11:13

Sede

61.5k20 gold badges158 silver badges162 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

jarry jafery Over a year ago

can you explain me a little about $$ROOT.

Sede Over a year ago

@jarryjafery $$ROOT is a system variable in MongoDB that gives you access to the document being processed. I will add an explanation to my answer when I have a little time.

lor1an Over a year ago

Why the second query is inefficient?

Equal · Accepted Answer · 2020-09-03 06:55:22Z

3

You can implement this easily with $facet

myCollection.aggregate([
    {
      $facet: {
        count: [{ $count: "value" }],
        data: [{ $sort: { _id: -1 } }, { $skip: skip }, { $limit: limit }]
      }
    },
    { $unwind: "$count" },
    { $set: { count: "$count.value" } }
])

the return result will be like:

[
  {
    "count": 234,
    "data": [
      // ...
    ]
  }
]

answered Sep 3, 2020 at 6:55

Equal

4045 silver badges9 bronze badges

Comments

Sridhar · Accepted Answer · 2018-08-09 07:51:58Z

0

@styvane I tested in person, this query is even less efficient than twice queries.

// get count 
db.course.aggregate([{$match:{}}, {$count: "count"}]);
    // get docs
db.course.aggregate(
       [
            {$match:{}},
            { "$sort": { "created_at": -1 }},
            {"$skip": offset},
            {"$limit": limit}
        ]
)

edited Aug 9, 2018 at 7:51

Sridhar

11.8k5 gold badges42 silver badges48 bronze badges

answered Aug 9, 2018 at 3:18

Jack LI

574 bronze badges

Comments

Meena · Accepted Answer · 2016-08-04 11:40:59Z

-5

No, there is no other way. Two queries - one for count - one with limit.

answered Aug 4, 2016 at 11:40

Meena

972 bronze badges

Collectives™ on Stack Overflow

find and count in single mongodb query

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related