JIYIK CN >

This article discusses operators in MongoDB, aggregation operators, and different ways to calculate the total number of records.

Operations in MongoDB

CRUD operations are a user interface concept that allows users to browse, search, and change objects in a database.

MongoDB documents are changed by connecting to the server, querying for the appropriate documents, and then transforming the data before sending it back to the database for processing. CRUD is a data-driven process that is standardized using HTTP action verbs.

1. Create − It is used to insert a new document in MongoDB database.
2. Read − It is used to query the documents in the database.
3. Update − It is used to modify the existing files in the database.
4. Delete − Deletes a document from the database.

Aggregation Operations in MongoDB

It is a data processing operation that consists of multiple stages that perform many operations on the grouped data to produce a single result. Following are the three options for performing aggregation operations.

1. Aggregation Pipeline – Documents are fed through a multi-stage pipeline that aggregates them into a single output. The MongoDB aggregation process is divided into multiple stages.

   An example is given below

   db.collection_name.aggregate([
   //First stage
   { $match: { status: "" } },
   //Second stage
   { $group: { _id: "$cust_id", total: { $sum: "$amount" } } },
   //Third Stage
    { $sort : {sort_field: -1 }}
   ])
   

2. Single Purpose Aggregation Methods − Single purpose aggregation methods are simple, but they lack the functionality of aggregation pipelines.
3. Map-Reduce - Map-reduce operations have been deprecated since MongoDB 5.0. Instead, use aggregation pipelines.

$count in MongoDB

Sends the document to the next step and counts the number of records it has received.

Behavior:

The following $group + $project sequence equals $count stages.

db.collection.aggregate( [
   { $group: { _id: null, myCount: { $sum: 1 } } },
   { $project: { _id: 2 } }
] )

where myCount indicates that the output field contains the count. You can specify a different name for the output field.

example:

A collection named scores has the given document.

{ "_id" : 1, "subject" : "English", "score" : 88 }
{ "_id" : 2, "subject" : "English", "score" : 92 }
{ "_id" : 3, "subject" : "English", "score" : 97 }
{ "_id" : 4, "subject" : "English", "score" : 71 }
{ "_id" : 5, "subject" : "English", "score" : 79 }
{ "_id" : 6, "subject" : "English", "score" : 83 }

The following aggregation operation has two phases.

1. The $match stage filters out documents with a score value less than or equal to 80, and allows documents with a score greater than 80 to enter the next stage.
2. The $count step counts the number of documents remaining in the aggregation pipeline and stores the result in a variable called passing_scores.

db.scores.aggregate(
  [
    {
      $match: {
        score: {
          $gt: 80
        }
      }
    },
    {
      $count: "passing_scores"
    }
  ]
)

The operation returns the following results.

{ "passing_scores" : 4 }

For fetching 1000 records, this takes an average of 2 milliseconds and is the fastest method.

db.collection.count() in MongoDB

Returns the number of records in a collection or view that match a find() query. db.collection.count()This function calculates and provides the number of results that match the query instead of the find() procedure.

Behavior:

You cannot use count or the shell tools count() and db.collection.count() within a transaction.

Sharded Cluster

Using db.collection.count() without a query predicate on a sharded cluster may result in incorrect counts if orphaned documents exist or if chunk migration is in progress.

Use the function on sharded clusters db.collection.aggregate()to prevent these situations.

To count documents, use the $count step. For example, the following procedure counts the documents in a collection.

db.collection.aggregate( [
   { $count: "myCount" }
])

The $count stage is equal to the following $group + $project sequence.

db.collection.aggregate( [
   { $group: { _id: null, count: { $sum: 1 } } }
   { $project: { _id: 0 } }
] )

Index usage

Consider a collection with the following index.

{ a: 1, b: 1 }

When counting, MongoDB can only use the index to return the count if you query:

1. You can use indexes,
2. Contains only conditions on index keys, and
3. Predicate accesses a single contiguous range of index keys

For example, given only the index, the following procedure can return the count.

db.collection.find( { a: 5, b: 5 } ).count()
db.collection.find( { a: { $gt: 5 } } ).count()
db.collection.find( { a: 5, b: { $gt: 10 } } ).count()

Suppose a query might use an index, but the predicate does not reach a contiguous range of index keys. The query also has conditions on fields outside the index.

In this case, MongoDB must read the document in addition to providing the count using the index.

db.collection.find( { a: 6, b: { $in: [ 1, 2, 3 ] } } ).count()
db.collection.find( { a: { $gt: 6 }, b: 5 } ).count()
db.collection.find( { a: 5, b: 5, c: 8 } ).count()

In this case, MongoDB pages the document into memory during the initial read, which speeds up subsequent calls to the same count operation.

Accuracy during unplanned downtime

After an unclean shutdown of a mongod using the Wired Tiger storage engine, the count statistics provided by count() may be incorrect.

The amount of drift is determined by the number of insert, update, or delete operations performed between the last checkpoint and the unclean shutdown.

Checkpoints occur on average every 60 seconds. On the other hand, mongod instances with a non-default -syncdelay option may have more or fewer checkpoints.

To restore statistics after an unclean shutdown, run verify on each collection on the mongod.

After abnormal shutdown:

1. validate updates the count statistics in the collStats output with the latest values.
2. Other statistics, such as the number of documents inserted or deleted in the collStats output, are estimates.

Client disconnected

Starting in MongoDB 4.2, if the client issuing a db.collection.count() disconnects before the operation completes, MongoDB marks the db.collection.count() as terminated using killOp.

Count all documents in a collection

To count the number of all records in the orders collection, use the following operation.

db.orders.count()

This operation is equivalent to the following operation.

db.orders.find().count()

Count all documents matching the query

Count how many documents in the orders collection have a field ord_dt greater than new Date('01/01/2012').

db.orders.count( { ord_dt: { $gt: new Date('01/01/2012') } } )

This query is equivalent to the following.

db.orders.find( { ord_dt: { $gt: new Date('01/01/2012') } } ).count()

MongoDB group by count

Each document in MongoDB _idhas its columns assigned a unique group by value. Aggregation techniques then process the data to produce a calculated result.

Here is an example.

This setup will be used in all code examples provided in this article.

db={
  "data": [
    {
      "_id": ObjectId("611a99100a3322fc1bd8c38b"),
      "fname": "Tom",
      "city": "United States of America",
      "courses": [
        "c#",
        "asp",
        "node"
      ]
    },
    {
      "_id": ObjectId("611a99340a3322fc1bd8c38c"),
      "fname": "Harry",
      "city": "Canada",
      "courses": [
        "python",
        "asp",
        "node"
      ]
    },
    {
      "_id": ObjectId("611a99510a3322fc1bd8c38d"),
      "fname": "Mikky",
      "city": "New Zealand",
      "courses": [
        "python",
        "asp",
        "c++"
      ]
    },
    {
      "_id": ObjectId("611b3e88a60b5002406571c3"),
      "fname": "Ron",
      "city": "United Kingdom",
      "courses": [
        "python",
        "django",
        "node"
      ]
    }
  ]
}

To query the above database, use:

db.data.aggregate([
  {
    $group: {
      _id: "ObjectId",
      count: {
        $count: {}
      }
    }
  }
])

See this code snippet working.

MongoDB sort group by count

This section uses $sortByCount, which has the same effect as $group + $sort. It can sort and count a group of people in ascending and descending order.

Given below is an example query. In this example, some documents have been added to the data collection and the find() method has been used to determine how many entries it contains.

Following will be the query for find().

db.data.find()

You can access the execution of this query from this link.

The next step is to pull down the courses array and use the $sortByCount function to count the number of records added to each course.

db.data.aggregate([
  {
    $unwind: "$courses"
  },
  {
    $sortByCount: "$courses"
  }
])

See how this query works with the above database configuration. This is the most straightforward way to do a MongoDB group by counting an array and sorting it.

MongoDB groups by counting multiple fields

MongoDB aggregate()functions may count multiple fields. Therefore, use $count to count the fields.

Below is an example where the timestamp is used for only one entry. In this example, the student collection can hold some documents, and you can use the find() method to see how many documents you have.

db.student.aggregate([ {$group: {_id: {name:"$name",
                                         timestamp:"$timestamp" }}},
                                        {$count:"timestamp"}
                      ])

MongoDB group by date and count

When you need to count the documents on a specific date, you can use the count aggregation and count the documents on a specific date.

Here is an example. In this example, you will learn to calculate the total sales and sales quantity for each day in 2021.

You can include product ID, item name, price, quantity, and date fields in the sales collection. You can get documents using the find() method.

db=
{
        "_id" : 1,
        "item" : "abc",
        "price" : NumberDecimal("10"),
        "quantity" : 2,
        "date" : ISODate("2021-03-01T08:00:00Z")
}
{
        "_id" : 2,
        "item" : "jkl",
        "price" : NumberDecimal("20"),
        "quantity" : 1,
        "date" : ISODate("2021-03-01T09:00:00Z")
}
{
        "_id" : 3,
        "item" : "xyz",
        "price" : NumberDecimal("5"),
        "quantity" : 10,
        "date" : ISODate("2021-03-15T09:00:00Z")
}
{
        "_id" : 4,
        "item" : "xyz",
        "price" : NumberDecimal("5"),
        "quantity" : 20,
        "date" : ISODate("2021-04-04T11:21:39.736Z")
}
{
        "_id" : 5,
        "item" : "abc",
        "price" : NumberDecimal("10"),
        "quantity" : 10,
        "date" : ISODate("2021-04-04T21:23:13.331Z")
}

The query for the above configuration would be:

db.date.aggregate([
  {
    $match : { "date": { $gte: new ISODate("2021-01-01"), $lt: new ISODate("2015-01-01") } }
  },

  {
    $group : {
       _id : { $dateToString: { format: "%Y-%m-%d", date: "$date" } },
       totalSaleAmount: { $sum: { $multiply: [ "$price", "$quantity" ] } },

       count: { $sum: 1 }
    }
  },
  {
    $sort : { totalSaleAmount: -1 }
  }
 ])

Now use the count and field commands to query single fields and groups of fields.

1. Single field group by and count

   db.Request.aggregate([
    {"$group" : {_id:"$source", count:{$sum:1}}}
   ])
   

2. Grouping and counting multiple fields

   db.Request.aggregate([
    {"$group" : {_id:{source:"$source",status:"$status"}, count:{$sum:1}}}
   ])
   

3. Multiple fields group by and sort count using fields

   db.Request.aggregate([
    {"$group" : {_id:{source:"$source",status:"$status"}, count:{$sum:1}}},
    {$sort:{"_id.source":1}}
   ])
   

4. Multiple fields group by and sort using count

   db.Request.aggregate([
    {"$group" : {_id:{source:"$source",status:"$status"}, count:{$sum:1}}},
    {$sort:{"count":-1}}
   ])
   

.toArray() method in MongoDB

toArray()The function returns an array containing all the documents in the cursor. The process iterates over the cursor several times, loading all the documents into RAM and exhausting the pointer.

Consider the following example, which uses toArray()the function to convert a cursor returned by the find() method.

var allProductsArray = db.products.find().toArray();

if (allProductsArray.length > 0) { printjson (allProductsArray[0]); }

The variable allProductsArray holds toArray()the array of documents returned by . It takes an average of 18 milliseconds to get 1000 records.

.itcount() in MongoDB

Counts the number of documents remaining in the cursor.

itcount()Similar to cursor.count(), but instead of running the query on a new iterator, it executes it on an existing iterator, exhausting its contents.

itcount()The prototype form of the method is as follows.

db.collection.find(<query>).itcount()

To fetch 1000 records, it takes an average of 14 milliseconds.

This article discusses operations in detail and also discusses aggregation operations. First, different types of aggregate functions are briefly discussed with code snippets.

Then group by and count were discussed, including sorting, searching, and multiple fields. Then different methods of counting records in MongoDB were discussed.