Introducing Lookup Charts in MongoDB

Alex Williams

If you aren’t familiar with MongoDB, it’s a document-oriented NoSQL data model, which uses documents rather than tables and rows like you would find with relational tables.

As such, due to the unique way it’s built, MongoDB is one of the best data models for high-performance databasing with great scalability. Of course, that doesn’t mean that it doesn’t have competition, and MongoDB is often compared to Firebase or Cassandra.

Of course, the problem then becomes that any query request on a datastore that large can be problematic, and requires a certain level of being a query guru. 

Thankfully, MongoDB has introduced a whole new feature that not only does away with code querying, it also makes it as simple as a few clicks. That means that you don’t have to expend a lot of time and hassle to do the same type of querying and joining as you would normally.

Traditional Querying in MongoDB

While document-oriented databases are already incredibly flexible, there are probably still situations where you might need live data in multiple collections. For example, one collection could contain user data and another could contain user activity. This could even be expanded to have several collections of data for different applications, websites, and so on.

That is why MongoDB Query Language (MQL) was born, and it provided a way for programmers to create complex queries. In fact, MongoDB has a whole page for query documents and how to run them. If you aren’t familiar with it, here’s a quick step-by-step process of how it works, so you can compare to the new LookUp Charts later:

First, you need to connect your MongoDB instance by passing the URI to the Mongo shell and then using --password

mongo.exe mongodb://$[hostlist]/$[database]?authSource=$[authSource] --username $[username]

Second, switch to the Database, in this case, we’ll use a hypothetical ‘test’ database

use test

At this point, you would load more data into MongoDB if needed. You can do that with the insertMany() method:

db.inventory.insertMany( [

   { "item": "journal", "qty": 25, "size": { "h": 14, "w": 21, "uom": "cm" }, "status": "A" },

    { "item": "notebook", "qty": 50, "size": { "h": 8.5, "w": 11, "uom": "in" }, "status": "A" },

    { "item": "paper", "qty": 100, "size": { "h": 8.5, "w": 11, "uom": "in" }, "status": "D" },

    { "item": "planner", "qty": 75, "size": { "h": 22.85, "w": 30, "uom": "cm" }, "status": "D" },

    { "item": "postcard", "qty": 45, "size": { "h": 10, "w": 15.25, "uom": "cm" }, "status": "A" }

]);

Then comes the actual query through retrieving documents in a specific collection:

myCursor = db.inventory.find( { status: "D" } )

Usually, this will show 20 documents and return a cursor, but if you want. If your result set is larger though, you’ll want to iterate over the results:

while (myCursor.hasNext()) {

print(tojson(myCursor.next()));

}

Finally, you’d check the results to make sure everything is correct. Keep in mind in the example below, your ObjectID values will differ:

{

 item: "paper",

 qty: 100,

 size: {

   h: 8.5,

   w: 11,

   uom: "in"

   },

 status: "D"

},

{

 item: "planner",

 qty: 75,

 size: {

   h: 22.85,

   w: 30,

   uom: "cm"

   },

 status: "D"

}

Benefits of LookUp Charts

As you can see, the process is quite complicated with many steps, and so it makes sense that MongoDB wanted to make the process a bit more streamlined. Of course, it goes a bit beyond just making things easier and there are a lot of benefits to LookUp Charts. 

For example, you can glean better insights through the single-view format by joining several collections. More importantly, having a visual and easy to parse chart updated live according to your specifications is invaluable. This often allows you to derive information nearly immediately just from visual inspection, especially if you divide information into further categories.

Finally, the biggest benefit is not needing to learn and master MQL for only one database, which reduces the barrier of entry for a lot of programmers.

How to Use LookUp Charts

Alright, so we’ve looked at how querying usually works on MongoDB, and we have a good idea of how LookUp Charts can help us get more salient information quicker, but how does it actually work? 

Well, the steps are relatively simple:

  1. First, you need to choose the data source by picking it on the drop-down menu in the top left.
  2. Then, click on the ‘ . . .’ of the field between your collections and click on ‘Lookup field’
  3. When the new window pops up, select the ‘Remote Data Source’ that’s where you’ll pull the data from.
  4. Then, you need to select ‘Remote Field’ and that would be the field that is common between your two data sources.
  5. Finally, you can save a specific name for the result field, and if not, just click on ‘Save’

And that’s pretty much it! You can now drag and drop from the new fields into the chart builder. Don’t forget to choose an Array Reduction method too, or else you might not see any chart showing up for you. 

Familiarizing Yourself with MongoDB Charts

Of course, it’s pertinent at this point to mention that the new LookUp feature is a part of MongoDB Charts, and MongoDB themselves have a few interesting articles to help you get your bearings with the software:

  1. New Ways to Customize Your Charts
  2. Visualizing Order Data
  3. Adding a Lookup Field (which is different from LookUp Charts)

Conclusion

As you can see, the new LookUp Charts is an incredibly powerful tool that massively cuts down on the technical knowledge of MongoDB querying. With just a few simple steps you can look at a chart of information joined from several collections, and understand new information almost immediately.

Compare that to the old method of doing it that required several steps of coding, as well as understanding that code, and you start to see how brilliant this new release is.

ClusterControl
The only management system you’ll ever need to take control of your open source database infrastructure.