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"
]
>
Note
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!!!

mongodb_basics_sort_count

Related Posts

Please Share Us on Social Media

Facebooktwitterredditpinterestlinkedinmail

Leave a Reply

Your email address will not be published. Required fields are marked *