“Count” and “Count Distinct” Queries in MongoDB

For the following examples, assume that you have a database that includes four records that include the following fields and values:

{ "_id" : ObjectId("54936…dd0c"), "last_name" : "smith", "first_name" : "mike" }
{ "_id" : ObjectId("54936…dd0d"), "last_name" : "smith", "first_name" : "william" }
{ "_id" : ObjectId("54936…dd0e"), "last_name" : "smith", "first_name" : "william" }
{ "_id" : ObjectId("54936…dd0f"), "last_name" : "smith", "first_name" : "mark" }

Note that there are four records with a last_name value of “smith”.  The four records have three distinct values for the first_name field (“mike, “william”, and “mark”).

To count the number of rows returned by a query, use "count()", as shown here:

> db.collection.find({“last_name”:”smith”}).count();

4

To count the unique values, use "distinct()" rather than "find()", and "length" rather than "count()".  The first argument for "distinct" is the field for which to aggregate distinct values, the second is the conditional statement that specifies which rows to select.  Append "length" to the end of the query to count the number of rows returned.  (The "count()" function does not work on the results of a "distinct" query.) 

Here is an example which counts the distinct number of first_name values for records with a last_name value of “smith”:

> db.collection.distinct("first_name", {“last_name”:”smith”}).length;

3

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: