CODEWITHSUNDEEP
mongodbmongodb-query
Amira Bedhiafi
Amira Bedhiafi

Reputation: 1

SELECT COUNT with HAVING clause

This is my input :

 {"_id": "phd/Klink2006","type": "Phd", "title": "IQForCE - Intelligent Query (Re-)Formulation with Concept-based Expansion", "year": 2006, "publisher": "Verlag Dr. Hut, M?nchen", "authors": ["Stefan Klink"], "isbn": ["3-89963-303-2"]}

I want to count books that have less than 3 authors. How can I reach this ?

Upvotes: 0

Views: 103

Answers (2)

Haniel Baez
Haniel Baez

Reputation: 1690

You can use the $where operator (this will return all the documents).

db.collection.find({
  "$where": "this.authors.length < 3"
});

Important consideration:

$where evaluates JavaScript and cannot take advantage of indexes. Therefore, query performance improves when you express your query using the standard MongoDB operators (e.g., $gt, $in).

In general, you should use $where only when you cannot express your query using another operator. If you must use $where, try to include at least one other standard query operator to filter the result set. Using $where alone requires a collection scan.

The best options in term of performance is to create a new key authorsLength

db.collection.aggregate([
  {
    "$match": {
      "authorsLength": {
        "$lt": 3
      }
    }
  },
  {
    "$group": {
      "_id": null,
      "count": {
        "$sum": 1
      }
    }
  }
])

Upvotes: 1

turivishal
turivishal

Reputation: 36144

  • $group by null, check condition if size of authors is less than 3 then count 1 otherwise 0
db.collection.aggregate([
  {
    $group: {
      _id: null,
      count: {
        $sum: {
          $cond: [
            { $lt: [{ $size: "$authors" }, 3] },
            1,
            0
          ]
        }
      }
    }
  }
])

Playground

Upvotes: 1

Related Questions