The distinct() function in MongoDB is used to retrieve distinct values. However, it has a problem. This function is a very basic function, it can only select distinct values for one field at a time. So, if there is a need to retrieve the complete set of records matching a specified query, it cannot be done using this function. This is where distinct aggregation can use.
Distinct aggregation is a powerful approach provided by the MongoDB aggregation framework. Using this you can do more complex operations by combining it with other aggregate operators like $group.
This tutorial will cover two ways to return distinct values in MongoDB. One involves using the distinct command, and the other utilizes the $group aggregate operator.
The purpose of this guide is to throw light on providing examples to demonstrate both approaches so that it becomes easier for you to understand their usage.
Syntax of Distinct Command
Distinct commands can be used to obtain a list of unique values that exist in the specified field of the documents within a collection.
Syntax:
Following is the syntax of the distinct command in MongoDB.
{
distinct: "collection",
key: "field",
query: query,
readConcern: read concern document,
collation: collation document,
comment: any
}
Note: If the specified field’s value is an array, distinct considers each element as a separate value. For instance, if a field contains the value [20, [20], 20], distinct considers the values 20, [20], and 20 as different values.
Syntax of Distinct Aggregation
Distinct Aggregation can be used in MongoDB when you need to perform more complex operations, including getting distinct values from a collection.
Syntax:
Below is the syntax for the distinct aggregation method which we will accomplish using the $group operator in MongoDB:
{
$group:
{
_id: expression, // Group By Expression
field1: { accumulator1 : expression1 },
...
}
}
Using the Distinct Command in MongoDB
Let’s first use the distinct command using the runCommand method to retrieve distinct values.
We have added multiple documents in a collection to query them using distinct commands and the $group aggregate operator.
Below is the collection we will be using for demonstrating all examples:
> db.drones.find().pretty()
{
"_id" : ObjectId("61673f46b34f185eb7b2bf0c"),
"utility" : [
"Natural Resource Exploration",
"Remote sensing",
"Real estate and construction",
"Recreation",
"Delivery"
],
"onSale" : false,
"name" : "Nimbari Gryphon Medeta 65",
"price" : 77500,
"weight" : "77 kilograms",
"additionalDetails" : {
"material" : "carbon fiber",
"moreUses" : [
"Precision Agriculture",
"Land Inspection",
"Water Inspection",
"Cinematography"
]
}
}
{
"_id" : ObjectId("61673f46b34f185eb7b2bf0d"),
"utility" : [
"Natural Resource Exploration",
"Remote sensing",
"Real estate and construction",
"Recreation",
"Delivery"
],
"onSale" : false,
"name" : "X-Strimmer Eye",
"price" : 23500,
"weight" : "24 kilograms",
"additionalDetails" : {
"material" : "glass fiber",
"moreUses" : [
"Precision Agriculture",
"Cinematography"
]
}
}
{
"_id" : ObjectId("61673f46b34f185eb7b2bf0e"),
"utility" : [
"Natural Resource Exploration",
"Remote sensing",
"Real estate and construction",
"Recreation",
"Delivery"
],
"onSale" : false,
"name" : "Khai Balemosh Shefqa TRX",
"price" : 120500,
"weight" : "80 kilograms",
"additionalDetails" : {
"material" : "aluminum",
"moreUses" : [
"Precision Agriculture",
"Land Inspection"
]
}
}
{
"_id" : ObjectId("61673f46b34f185eb7b2bf0f"),
"utility" : [
"Natural Resource Exploration",
"Recreation",
"Delivery"
],
"onSale" : false,
"name" : "Sifinist Croma AX",
"price" : 99500,
"weight" : "97 kilograms",
"additionalDetails" : {
"material" : "lithium",
"moreUses" : [
"Precision Agriculture",
"Land Inspection",
"Water Inspection",
"Videography"
]
}
}
{
"_id" : ObjectId("61673f46b34f185eb7b2bf10"),
"utility" : [
"Remote sensing",
"Real estate and construction",
"Recreation"
],
"onSale" : false,
"name" : "Drovce Finnifield FR-7",
"price" : 87600,
"weight" : "13 kilograms",
"additionalDetails" : {
"material" : "polysterene",
"moreUses" : [
"Precision Agriculture",
"Land Inspection",
"Water Inspection",
"Videography"
]
}
}
Also read: Pretty Print in MongoDB
Now let’s retrieve distinct values for the “utility” field from the “drones” collection.
Example:
> db.runCommand( { distinct: "drones", key: "utility" } )
{
"values" : [
"Delivery",
"Natural Resource Exploration",
"Real estate and construction",
"Recreation",
"Remote sensing"
],
"ok" : 1
}
In the above output, you can see that we have successfully rendered unique values for the utility field using the distinct command.
Using the Distinct Aggregation in MongoDB
Now let’s see different examples of how to retrieve unique values using distinct aggregation.
Distinct Aggregation on a Top-Level Field in MongoDB using $group Operator
In this example, we will be performing aggregation operation on the “drones” collection using the aggregate method with $group operator.
If you want to learn more about this, we have a separate tutorial on the MongoDB aggregate $group stage.
Example:
> db.drones.aggregate( [ { $group : { _id : "$price" } } ] )
{ "_id" : 120500 }
{ "_id" : 99500 }
{ "_id" : 87600 }
{ "_id" : 77500 }
{ "_id" : 23500 }
In the result, each data represents a distinct value from the “price” field, where “id” holds the value itself.
Distinct Aggregation on an Embedded Field in MongoDB using $group Operator
In this example, we will operate on an embedded field to render distinct values using the distinct aggregation in MongoDB.
Example:
> db.drones.aggregate( [ { $group : { _id : "$additionalDetails.material" } } ] )
{ "_id" : "lithium" }
{ "_id" : "polysterene" }
{ "_id" : "carbon fiber" }
{ "_id" : "aluminum" }
{ "_id" : "glass fiber" }
Using Mongodb distinct aggregation, we successfully got the list of documents, each containing a distinct value found in the “additionalDetails.material” field. Each document’s “_id” field represents a distinct value from the “additionalDetails.material” field.
Conclusion
We have successfully learned about Distinct Aggregation in MongoDB. While it may initially view challenging to understand, with some practice, it becomes an easy concept to grasp. By working with examples and experimenting, you will gain a better understanding of how to use Distinct Aggregation effectively.
We have only used the $group for simplicity but you can also incorporate stages like $match and $project in the aggregation pipeline. We hope you have found this tuitorial informative and helpful.
References
- https://docs.mongodb.com/manual/reference/command/distinct/
- https://docs.mongodb.com/manual/reference/operator/aggregation/group/
- https://stackoverflow.com/questions/16368638/mongodb-distinct-aggregation
