MongoDB Basics – Finding Distinct Values, Using Sort and Finding the Number of Documents in a Collection
MongoDB Basics – Finding Distinct Values, Using Sort and Finding the Number of Documents in a Collection
In this tutorial we learn about how to use Distinct to find unique fields within a document. In addition, we learn about using Sort and Count methods in MongoDB.
Using Distinct
db.collection.distinct(field, query)
Finds the distinct values for a specified field across a single collection and returns the results in an array.
Show All Documents in Employee Collection
For this example, let’s assume these are all of the names in the employee collection.
>db.employee.find({},{_id:0}) {"first_name":"John","last_name":"Smith","pay_rate":57000,"active":1} {"first_name":"Jack","last_name":"Walder","pay_rate":36000,"active":1} {"first_name":"Robert","last_name":"Valle","pay_rate":72000,"active":1} {"first_name":"John","last_name":"Holden","pay_rate":94500,"active":1} {"first_name":"Olivia","last_name":"Wilde","pay_rate":124500,"active":1} {"first_name":"Amaury","last_name":"Valdes","pay_rate":59800,"active":1} {"first_name":"John","last_name":"Fitzgerald","pay_rate":79750,"active":1} {"first_name":"John","last_name":"Henry","pay_rate":43850,"active":1} {"first_name":"David","last_name":"LaSalle","pay_rate":58500,"active":1} {"first_name":"Halle","last_name":"Gululi","pay_rate":85000,"active":1} {"first_name":"Julie","last_name":"Mehaul","pay_rate":73200,"active":1} {"first_name":"Jennifer","last_name":"Stevenson","pay_rate":84000,"active":1} >
Using Distinct for a Specific Field
Let’s show all the distinct values (unique values) for the field called first_name using the distinct method.
> db.employee.distinct("first_name") [ "John", "Jack", "Robert", "Olivia", "Amaury", "David", "Halle", "Julie", "Jennifer" ] >
This is a similar exercise using last_name field now.
> db.employee.distinct("last_name") [ "Smith", "Walder", "Valle", "Holden", "Wilde", "Valdes", "Fitzgerald", "Henry", "LaSalle", "Gululi", "Mehaul", "Stevenson" ]
Using Distinct with a Query
This next example will return the distinct number of values for the last_name field from the employee collection whose pay_rate is greater than or equal to $50,000.
> db.employee.distinct("last_name", {pay_rate: {$gte:50000}}) [ "Smith", "Valle", "Holden", "Wilde", "Valdes", "Fitzgerald", "LaSalle", "Gululi", "Mehaul", "Stevenson" ] >
Counting the Number of Elements Returned in Distinct Method
In order to count the number of elements in the array returned by the distinct method you must use the length operator.
> db.employee.distinct("first_name").length 9
Using Sort() in Ascending Order
db.employee.find(query, projection).sort(sortDoc)
In these next two sorting examples I am using projection to limit the number of fields returned from the find query by removing both the _id and active fields. I then choose the field to sort, in my case, the last_name field and the sort order denoted by a 1.
> db.employee.find({}, {_id:0,active:0}).sort({last_name: 1}) { "first_name" : "John", "last_name" : "Fitzgerald", "pay_rate" : 79750 } { "first_name" : "Halle", "last_name" : "Gululi", "pay_rate" : 85000 } { "first_name" : "John", "last_name" : "Henry", "pay_rate" : 43850 } { "first_name" : "John", "last_name" : "Holden", "pay_rate" : 94500 } { "first_name" : "David", "last_name" : "LaSalle", "pay_rate" : 58500 } { "first_name" : "Julie", "last_name" : "Mehaul", "pay_rate" : 73200 } { "first_name" : "John", "last_name" : "Smith", "pay_rate" : 57000 } { "first_name" : "Jennifer", "last_name" : "Stevenson", "pay_rate" : 84000 } { "first_name" : "Amaury", "last_name" : "Valdes", "pay_rate" : 59800 } { "first_name" : "Robert", "last_name" : "Valle", "pay_rate" : 72000 } { "first_name" : "Jack", "last_name" : "Walder", "pay_rate" : 36000 } { "first_name" : "Olivia", "last_name" : "Wilde", "pay_rate" : 124500 } >
Using Sort() in Descending Order
In this example, we sort last_name field in descending order by passing a -1 in the sort document.
> db.employee.find({}, {_id:0,active:0}).sort({last_name: -1}) { "first_name" : "Olivia", "last_name" : "Wilde", "pay_rate" : 124500 } { "first_name" : "Jack", "last_name" : "Walder", "pay_rate" : 36000 } { "first_name" : "Robert", "last_name" : "Valle", "pay_rate" : 72000 } { "first_name" : "Amaury", "last_name" : "Valdes", "pay_rate" : 59800 } { "first_name" : "Jennifer", "last_name" : "Stevenson", "pay_rate" : 84000 } { "first_name" : "John", "last_name" : "Smith", "pay_rate" : 57000 } { "first_name" : "Julie", "last_name" : "Mehaul", "pay_rate" : 73200 } { "first_name" : "David", "last_name" : "LaSalle", "pay_rate" : 58500 } { "first_name" : "John", "last_name" : "Holden", "pay_rate" : 94500 } { "first_name" : "John", "last_name" : "Henry", "pay_rate" : 43850 } { "first_name" : "Halle", "last_name" : "Gululi", "pay_rate" : 85000 } { "first_name" : "John", "last_name" : "Fitzgerald", "pay_rate" : 79750 } >
Using Sort() with Multiple Fields
In this example, we will structure our sort document such that first_name field is sorted first in ascending order followed by last_name also in ascending order. I chose this because of the fact we happened to have multiple ‘John’ users in the employee collection.
> db.employee.find({}, {_id:0,active:0}).sort({first_name: 1, last_name: 1}) { "first_name" : "Amaury", "last_name" : "Valdes", "pay_rate" : 59800 } { "first_name" : "David", "last_name" : "LaSalle", "pay_rate" : 58500 } { "first_name" : "Halle", "last_name" : "Gululi", "pay_rate" : 85000 } { "first_name" : "Jack", "last_name" : "Walder", "pay_rate" : 36000 } { "first_name" : "Jennifer", "last_name" : "Stevenson", "pay_rate" : 84000 } { "first_name" : "John", "last_name" : "Fitzgerald", "pay_rate" : 79750 } { "first_name" : "John", "last_name" : "Henry", "pay_rate" : 43850 } { "first_name" : "John", "last_name" : "Holden", "pay_rate" : 94500 } { "first_name" : "John", "last_name" : "Smith", "pay_rate" : 57000 } { "first_name" : "Julie", "last_name" : "Mehaul", "pay_rate" : 73200 } { "first_name" : "Olivia", "last_name" : "Wilde", "pay_rate" : 124500 } { "first_name" : "Robert", "last_name" : "Valle", "pay_rate" : 72000 } >
Using Sort() Method with Distinct
To sort the array of elements list from the distinct method we can call sort() right after.
> db.employee.distinct("first_name").sort() [ "Amaury", "David", "Halle", "Jack", "Jennifer", "John", "Julie", "Olivia", "Robert" ] >
Using Sort() Method with Distinct* for Descending Order
This is NOT working correctly at this point.
> db.employee.distinct("first_name").sort({first_name:-1}) [ "Amaury", "David", "Halle", "Jack", "Jennifer", "John", "Julie", "Olivia", "Robert" ] >
Some versions of MongoDB do not support sort() from the distinct() method and it appears to be working only in ascending order. The descending order does NOT work as I would have hoped!!!
Finding the Number of Documents in a Collection
db.collection.find().count()
To find the number of documents in the collection make sure you leave the query document empty.
> db.employee.find().count() 12
Finding the Number of Documents in a Cursor
db.collection.find(query).count()
Counts the number of documents referenced by a cursor, in our example our find() query returns a cursor to the documents not the actual documents. The count() method appended to the find() query will return the number of documents contained in that cursor.
> db.employee.find({pay_rate: {$gte:50000}}, {_id:0, active:0}) { "first_name" : "John", "last_name" : "Smith", "pay_rate" : 57000 } { "first_name" : "John", "last_name" : "Smith", "pay_rate" : 57000 } { "first_name" : "John", "last_name" : "Holden", "pay_rate" : 94500 } { "first_name" : "Amaury", "last_name" : "Valdes", "pay_rate" : 86500 } { "first_name" : "David", "last_name" : "LaSalle", "pay_rate" : 58500 } { "first_name" : "Jennifer", "last_name" : "Angeline", "pay_rate" : 96500 } { "first_name" : "Halle", "last_name" : "Gululi", "pay_rate" : 85000 } { "first_name" : "Julie", "last_name" : "Mehaul", "pay_rate" : 73200 } { "first_name" : "Jennifer", "last_name" : "Stevenson", "pay_rate" : 84000 } > db.employee.find({pay_rate: {$gte:50000}}, {_id:0, active:0}).count() 9 >
That’s It!
I hope you enjoyed this tutorial. It was certainly a lot of fun putting it together and testing it out. Please continue to share the love and like us so that we can continue bringing you quality tutorials. Happy Coding!!!
Related Posts
- MongoDB Tutorials – Installation, Basics, Core, JAX-RS and Spring Data Examples
This is an index post containing a consolidated list of all of the Mongo related tutorials as well as those using other Frameworks like Spring MVC/Spring Data, and JAX-RS. - MongoDB Setup – Installation, Configure and Set up Windows Service
This MongoDB Setup -tutorial will guide you through installation, configuration setup and show you how to configure MongoDB as a Windows Service. - MongoDB Shell Basics – Insert, Update, Find, Delete and Indexing
In this, MongoDB Shell Basics tutorial we will take you through the basics of using the mongo shell to create, update, delete and find documents in collections. In addition we will show you howto create indexes in collections. - MongoDB Basics – Finding Distinct Values, Using Sort and Finding the Number of Documents in a Collection
In this tutorial we learn about how to use Distinct to find unique fields within a document. In addition, we learn about using Sort and Count methods in MongoDB. - MongoDB Basics – Aggregation and Group Examples Tutorial
In this tutorial we learn about how to use aggregation operations to process data matching a certain criteria and perform some operation to return computed results. - Connecting to MongoDB using JDBC
The JDBC API defines a set of interfaces and classes that all major database providers adhere to in order allow Java developers to seamlessly connect to many Relational Database Management Systems (RDBMS). All major vendors provide their own JDBC drivers which contain a set of java classes that enables you to connect to that particular database. - Java Connecting to MongoDB 3.2 Examples
In this tutorial, Java Connecting to MongoDB 3.2 Examples we will show you different ways to connect to the latest version of MongoDB using Java and their mongo java driver (mongo-java-driver-3.2.0.jar). - MongoDB Java CRUD Operations Example Tutorial
In this tutorial we will focus on using CRUD Operations (Create, Read, Update and Delete) with the latest version of MongoDB using Java and MongoDB Java Driver (mongo-java-driver-3.2.0.jar). We will focus our efforts on insertOne, updateOne, replaceOne, findOneAndUpdate and findOneAndDelete. - MongoDB Java using Find and Query Operations Example Tutorial
In this tutorial we will focus on using Find and Query Operations to retrieve data from a MongoDB collection. We will concentrate on using the following MongoDB operators: ($gt, $lt, $gte, $lte, $in, $nin, $and and $or) with the latest version of MongoDB using Java and MongoDB Java Driver (mongo-java-driver-3.2.0.jar).
Leave a Reply