Studio 3T: SQL Exploration for MongoDB

Studio 3T, a cross-platform MongoDB GUI and IDE, has provides a SQL-based user interface for issuing queries against MongoDB. Studio 3T is available in a range of commercial versions, and several new features like the SQL Query and Query Code generation features require the Studio 3T Pro version.

The ability for users to directly query and explore data has long been a hallmark of professional databases. With a vendor supplied tool and a basic understanding of SQL, users can query any type of data without extensive training in that particular product. The main exception to this is the so-called NoSQL databases. With each NoSQL database needing its own specialized syntax, training costs can skyrocket.

Studio 3T seeks to address this by providing a SQL-based user interface. This user interface looks remarkably similar to what you would find in SQL Server Management Studio or PosgreSQL's pgAdmin. It has the standard three-panel layout with the list of the collections on the left, an SQL editor on the top-right, and the results pane on the bottom-right.

Like many SQL-based tools, you can directly edit records in Studio 3T. While this can be useful for manual data correction, normally one would set the tool into "read-only mode" when connecting to a production database to reduce the risk of making a mistake.

SQL to JavaScript (node.JS), Java, Python, and C# Code

A unique feature of Studio 3T is the ability to convert SQL to language specific code. After verifying the query is working, you can switch to the "Query Code" tab to see what it would look like in JavaScript (node.JS), Java, Python, or C#. Besides being a useful training tool, this can save a lot of time that would otherwise be spent running lengthy integration tests.

Developers using this feature should also take advantage of the "explain query" support. As in other database engines, this will give you important information about the expected performance characteristics of the query.

Joins for MongoDB

Studio 3T adds new features fairly frequently and among the more recent updates is the ability to perform SQL-style inner and left joins against MongoDB data sets​. These joins are expressed in MongoDB's native query language using the $lookup operator. This creates some limitations in the SQL implementation. While multiple joins are supported, each join can only reference tables to the left of it. Or in other words, right joins are not supported. Likewise full joins cannot be used.

Other SQL Features

Studio 3T's SQL support also includes:

• GROUP BY, ORDER BY and Aggregate Functions
• Limit and Offset (i.e. data pagination)
• Wildcards using LIKE
• Testing If a Value Is a Member of a Set using IN
• Testing If a Value Lies within a Range using BETWEEN
• Accessing Embedded Fields Using Dotted Names

This last one allows you to use standard OOP style dot-notation to read child fields. For example, to read a zip code you would use "address.zip_code". As with many database engines, field names may be surrounded with brackets or double quotes. Single quotes are used for string literals.