mongodb MongoDB $Min, $Max, and $Avg Operators

[Linux Hint]

MongoDB is very vast in terms of having different special-purpose operators. These operators can be applied to strings, integers, and other types of values or fields. There are also some comparison operators available like the min and max operators. The MongoDB min operator is used to find the lowest value in a specified field of a collection while the MongoDB max operator is used to find the highest value in a specified field of a collection. Along with that, the MongoDB avg operator is cast-off to compute the average value of a specified field. In this guide, we will discuss the use of each operator one by one.

Create a Collection

The “db.createCollection(“Order”)” command is employed to make a new collection in a MongoDB database. The collection is named “Order” in this case. When the command is executed, the output is “{ ok: 1 }”. This indicates that the collection is effectively created. The “ok” field in the output is a status indicator that returns a value of 1 if the operation is successful and a value of 0 if it is not. It’s important to note that if a collection with the same name already exists in the database, the command returns an error.

test> db.createCollection("Order")
{ ok: 1 }

Insert the Documents

The “db.Order.insertMany()” command inserts multiple documents into the “Order” collection in the “test” database. The documents that are inserted are an array of objects that contain an “id”, “Title”, and “Price” fields. The documents contain the same values for the “title” fields in some records and it contains unique records in the “Price” field. The output of the command is a document which confirms that the insertion is acknowledged (acknowledged: true) and shows the unique ObjectIds that are assigned to each of the inserted documents.

test> db.Order.insertMany([{ id: 1, Title: "Soap", Price: 44 }, { id: 2, Title: "Shampo", Price: 94 }, { id: 3, Title: "Facewash", Price: 120 }, { id: 4, Title: "Soap", Price: 65 }, { id: 5, Title: "Shampo", Price: 78 }])
{
  acknowledged: true,
  insertedIds: {
    '0': ObjectId("63c4eec7ac8011ecc27d7a40"),
    '1': ObjectId("63c4eec7ac8011ecc27d7a41"),
    '2': ObjectId("63c4eec7ac8011ecc27d7a42"),
    '3': ObjectId("63c4eec7ac8011ecc27d7a43"),
    '4': ObjectId("63c4eec7ac8011ecc27d7a44")
  }
}

Display the Records

The “db.Order.find({})” command is executed in the MongoDB shell and is used to retrieve all documents in the “Order” collection. The empty curly braces indicate that no specific query criteria are used to filter the documents, so all documents in the collection are returned. The output of the command is a list of JSON objects, each representing a document in the “Order” collection.

test> db.Order.find({})
[
  { _id: ObjectId("63c4eec7ac8011ecc27d7a40"), id: 1, Title: 'Soap', Price: 44 },
  { _id: ObjectId("63c4eec7ac8011ecc27d7a41"), id: 2, Title: 'Shampo', Price: 94  },
  { _id: ObjectId("63c4eec7ac8011ecc27d7a42"), id: 3, Title: 'Facewash', Price: 120 },
  { _id: ObjectId("63c4eec7ac8011ecc27d7a43"), id: 4, Title: 'Soap', Price: 65  },
  { _id: ObjectId("63c4eec7ac8011ecc27d7a44"), id: 5, Title: 'Shampo', Price: 78  }
]

Example 1: MongoDB $Min Operator

In the first example of this article, we will discuss the use of the “min” operator with the help of simple MongoDB illustrations. The following command uses the MongoDB aggregate function to group the documents in the “Order” collection by the “Title” field. Then, it calculate the minimum “Price” within each group. The first argument that is passed to the aggregate function is an array of pipeline stages. In this case, there is only one pipeline stage which uses the $group operator to group the documents by the “Title” field which is specified as the “_id” field in the $group operator. The operator also calculates the minimum “Price” within each group and assigns it to the “MinPrice” field.

The resulting output is an array of objects, with each object representing a group of documents with the same “Title” field. The output shows that there are three groups of documents with different “Title” fields – “Soap”, “Shampoo” and “Facewash”. The minimum price for each group is 44, 78, and 120, respectively.

test> db.Order.aggregate([ {$group: { _id: "$Title", MinPrice: { $min: "$Price" } }} ])
[
  { _id: 'Soap', MinPrice: 44 },
  { _id: 'Shampo', MinPrice: 78 },
  { _id: 'Facewash', MinPrice: 120 }
]

Example 2: MongoDB $Max Operator

Here are some examples to discuss and use the “max” operator in MongoDB. The attached instruction uses the MongoDB aggregate function to group the Order collection by the “Title” field. Within the group, it creates two new fields: “PricewithTax” and “MaxPrice”. The “PricewithTax” field uses the $max operator to find the maximum value of the result of the $add operator. The $add operator adds 10 to the “Price” field for each document in the group. This means that the “PricewithTax” field has the highest “Price” field value plus 10 for each group of documents with the same “Title”.

The “MaxPrice” field uses the $max operator to find the maximum value of the “Price” field for each group of documents with the same “Title”. The output of the command is an array of documents that shows the “Title”, “PricewithTax”, and “MaxPrice” fields for each group of documents in the Order collection. The documents are ordered by the “Title” field. For example, the first document in the output shows that the “Title” is “Shampo”, the “PricewithTax” is 104 (the highest “Price” field value plus 10 for all documents with the “Title” of “Shampo”), and the “MaxPrice” is 94 (the highest “Price” field value for all documents with the “Title” of “Shampo”).

test> db.Order.aggregate([{ $group: { _id: "$Title", PricewithTax: { $max: { $add: ["$Price", 10] } }, MaxPrice: { $max: "$Price" } } }])
[
  { _id: 'Shampo', PricewithTax: 104, MaxPrice: 94 },
  { _id: 'Facewash', PricewithTax: 130, MaxPrice: 120 },
  { _id: 'Soap', PricewithTax: 75, MaxPrice: 65 }
]

Example 3: MongoDB $Avg Operator

In our last illustration of this guide, we cover the MongoDB “avg” operator to find out the average value from a specific field of collection. The MongoDB aggregate function is used to group the documents within the “Order” collection by their “Title” field. Within each group, it calculates the average “Price” value and creates a new field called “AvgPrice” to store that value. The result of this command is an array of objects, with each object representing a group of documents with the same “Title” value. The object contains the “Title” value as the “_id” field and the calculated average “Price” value as the “AvgPrice” field.

In this instance, there are three groups: “Soap”, “Shampo”, and “Facewash”. The average price for the “Soap” group is 54.5. The average price for the “Shampo” group is 86. And the average price for the “Facewash” group is 120, as follows:

test> db.Order.aggregate([{ $group: { _id: "$Title", AvgPrice: { $avg: "$Price" } } }])
[
  { _id: 'Soap', AvgPrice: 54.5 },
  { _id: 'Shampo', AvgPrice: 86 },
  { _id: 'Facewash', AvgPrice: 120 }
]

Conclusion

The introduction of our article explains the uses and differences of the three very common and most-used operators in MongoDB – the min, max, and avg operators. By going all out with the examples, we explained the methods to use these operators in three separate illustrations. The first illustration explains the min operator, the second illustration explains the “max”, while the third illustration explains the “avg” operator usage. If you have a collection of documents which represents some numerical values, you can try the min, max, and avg operators to find the lowest, highest, and average values.

View the full article