Mongo DB - Aggregations

In this article we will look at Mongo DB aggregations. The syntax is slightly complex, but we will try to break it down into simple sections and see how to use the power of aggregations while querying Mongo DB.

First, let us begin with a simple example. Let us count the number of books written by anonymous authors. Use the following Mongo DB aggregation query.

db.Books.count({author:"Anonymous"});

That one above was quite simple and self explanatory. Now let us take another simple example. Let us find out a list of distinct publishers. Use the following Mongo DB aggregation query.

db.Books.distinct("publisher");

Now let us look at a little more complex query. Let's calculate the number of books per publisher. The Mongo DB aggregate query would look like the one below.

db.Books.aggregate(

  [

    {$match: {} },

    {$group: {_id: "$publisher", title: {$count: "$title"} } }

  ]

);

In the above query notice that there are two major parts. The first part, the '$match' portion of the query is used to specify the filter condition. Here we fetched all the documents in the collection. You may use this as per your specific requirement.

In the second part, the '$group' portion of the query notice that we are specifying two sub-items: the '_id'  specifies the attribute on which grouping will be done before aggregation can occur. Whereas, the second part specifies the actual attribute whose values will be aggregated.

So in the above query we are instructing the Mongo DB engine to group by 'publisher' and find out the 'count' (i.e. number) of 'title' (i.e. books).

You may also apply some sorting along with Mongo DB aggregate queries as shown below. The below query will sort the output of the Mongo DB aggregation query in ascending order. Note that for descending order sorting we can use -1.

db.Books.aggregate(

  [

    {$match: {} },

    {$group: {_id: "$publisher", title: {$count: "$title"} } }

    {$sort: {total: 1} }

  ]

);

Mongo DB - Deleting Documents from Collections

In this article we will see how to delete data in Mongo DB collections.

For deleting a single record, the following delete query can be used. Note that this query has one part inside the remove() method. This part is for filtering the documents that have to be deleted. The following query will delete the only that particular book with the specified object ID.

db.Books.remove({_id: ObjectId("1234d4ef422drf433rffer4d2445f43")});

For deleting multiple records, the following delete query can be used. Note that this query also has only one part inside the delete() method. This part is for filtering the documents that have to be updated. The following delete query will delete all the books whose author is 'Anonymous'.

db.Books.remove({author: "Anonymous"});

Hope you find this crisp and useful.

Cheers!

Mongo DB - Updating Documents

In this article we will see how to update data in Mongo DB collections.

For updating a single record, the following update query can be used. Note that this query has two parts inside the update() method. The first part is for filtering the documents that have to be updated. While the second part is the target attribute and value that has to be updated.

db.Books.update({_id: ObjectId("1234d4ef422drf433rffer4d2445f43")}, {$set: {title: "Learning"}});

For updating multiple records, the following update query can be used. Note that this query has three parts inside the update() method. The first part is for filtering the documents that have to be updated. While the second part is the target attribute and value that has to be updated. The third part is important to explicitly specify that this query will affect multiple documents - by default this is set to false.

db.Books.update({author: /Anonymous/}, {$set: {author: "Anon"}}, {multi: true});

Hope you find this crisp and useful.

Cheers!

Mongo DB - Selecting Data

The following query will fetch the documents where the author name is equal to 'Anonymous'.

db.Book.find({author: "Anonymous"});

Find books priced below Rs 200 /-.

db.Books.find({price: {$lt: 200}});

Find books priced above Rs 200 /-.

db.Books.find({price: {$gt: 200}});

The following query will fetch the documents where the title of a book has the word 'earn'.

db.Book.find({title: /earn/});

The following query will fetch the documents where the title of a book starts with the word 'Learn'.

db.Book.find({title: /^Learn/});

Now let us see how to combine multiple filters. The below example returns documents where the title of the book starts with the word 'Learn' and also the price is less than Rs 200 /-. We will be using a comma (',') to combine multiple filters.

db.Books.find({title: /^Learn/}, {price: {$lt: 200}});

The next query can be used to sort results. The following query sorts all book by price in ascending order. Note that '1' implies an ascending order, while '-1' would imply a descending order.

db.Books.find({}).sort({price: 1});

Cheers.

Mongo DB - Working with Collections

Let us say we have a collection named 'Books' In the next few examples we will learn how to insert and select documents to and from this collection.

Use the following command to add/insert a document into a collection. Note that a column named "_id" will be automatically populated with a GUID (Global unique identifier) value every time a new document is inserted.

SYNTAX: db.<CollectionName>.insert(<DocumentInJsonFormat>);

EXAMPLE: db.Books.insert({title: "Learn MongoDB", author: "Anonymous", year: 2019});

You can use the following command to insert multiple documents at once. The box ('[' and ']') brackets are used to enclose multiple Json documents in an array of Json objects.

db.Books.insertMany([

  {title: "Learn MongoDB", author: "Anonymous", year: 2019},

  {title: "Learn NoSQL", author: "Anonymous", year: 2020}

]);

It is to be noted that there are two other variations of the insert command: insert() and insertOne(). insert() is similar to insertMany() - however, it has been deprecated in newer versions of Mongo engine. Whereas, the insertOne() command can be used similar to insertMany() - however, only for inserting a single document.

Following query returns all documents in the 'Books' collection.

db.Books.find({});

A more readable (well formatted Json) output can be obtained using the 'pretty()' function as shown below.

db.Books.find({}).pretty();

Use the following query to count the number of documents in a collection.

db.Books.find({})count();

The following query can be used to selectively display attributes from documents. The following query will return the columns: '_id', 'title' and 'author'. The 'year' attribute will not be returned. Specifying the value '1' after an attribute name will select that attribute. Similarly, specifying the value '0' or not specifying the attribute name altogether will suppress the attribute from appearing in the output result set.

db.Books.find({}, {title:1, author:1});

Notice above that by default the '_id' field is always displayed in the result set. You can suppress it by explicitly specifying '0' against it, as shown below.

db.Books.find({}, {_id:0, title:1, author:1});

Cheers.

Mongo DB - Basic Commands

This article aims to provide a list of basic commands used to navigate around in Mongo DB. All queries shown below have been tested in the MongoDB shell.

The following command will display a list of databases.

show dbs

The following command will create a new Mongo DB database (if it does not already exist). If the database already exists then the execution context will be switched to the named (in this example 'BookLibrary') database.

use BookLibrary

The following command will list the name of the database in which the current execution context is tagged with.

db

Use the following command to create a new collection. The following command shown below will create a collection named 'Books".

db.createCollection("Books")

The following command wil list the collections in the current database.

show collections

Use the following command to drop a collection. The below command drops a collection named 'Books'.

db.Books.drop()

In order to drop a database, use the following command. The below command drops a database named 'BookLibrary'.

db.DropDatabase()

These are some frequently used basic commands that will come handy when working with Mongo DB.

Rapidminer - Create a Basic Process

The typical Rapidminer IDE and its general layout is shown below.

After opening the IDE, the first step is to import data. Click on "Import Data" and then in the pop-up screen click on "My Computer".

Next, browse to your computer disk and choose the data file (in this case a csv file containing share prices)

After this, click on the "Next" button. Rapidminer will show you a preview of the contents of the file being uploaded.

Check whether everything is fine. Then click on the "Next" button. The next screen will allow us to make any changes we may need to the data types and column properties.

Click "Next" after making changes as needed.

Choose a location and click "Finish".

The data has now been uploaded into Rapidminer's local repository.

After this you can start building various processes using machine learning models!

Cheers,

Kallol