BT

Studio 3T: SQL Exploration for MongoDB

| by Jonathan Allen Follow 595 Followers on Jun 12, 2018. Estimated reading time: 2 minutes |

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.

Rate this Article

Adoption Stage
Style

Hello stranger!

You need to Register an InfoQ account or or login to post comments. But there's so much more behind being registered.

Get the most out of the InfoQ experience.

Tell us what you think

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread

Interesting but by Damien Pal

... so expensive !
For example, JetBrains IntelliJ IDEA Ultimate, ReSharper Ultimate and other IDE
1550 € / 3 years
Studio 3T Pro Edition: 900 € / 3 years
Pity

Re: Interesting but by Jonathan Allen

True, but you have to balance that against the time you'll save with a tool like this. Especially during a production outage, where every minute of down time equates can equate to thousands of dollars of lost revenue.

And then there are the contractor/consultant rates. With even mid-grade consultants charging more than $100/hr, it only has to save me 2-3 hours of billable work to pay for itself.

P.S. I do have to stress that I am not personally endorsing Studio 3T. But this general type of product is something that, like training and decent chairs, I feel companies should invest in.

Re: Interesting but by Kathryn Vargas

With the available multi-year discount which gives you 50% off years 2 and 3, a three-year Pro license would only cost around $600, which translates to roughly $16/month (compared with $42/month for IntelliJ).

Re: Interesting but by Damien Pal

Companies have to invest in a lot of very useful systems and software. Since we can not invest everywhere or say yes to everything, it's a question of quality / price ratio.
I think it's not good enough here

Re: Interesting but by Damien Pal

Thanks for the point

But for Jetbrains, 42 € / month with
- IntelliJ IDEA Ultimate,
- Rider + ReSharper Ultimate
- WebStorm,
- DataGrip,
- lot of ides/tools
...

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread

5 Discuss

Login to InfoQ to interact with what matters most to you.


Recover your password...

Follow

Follow your favorite topics and editors

Quick overview of most important highlights in the industry and on the site.

Like

More signal, less noise

Build your own feed by choosing topics you want to read about and editors you want to hear from.

Notifications

Stay up-to-date

Set up your notifications and don't miss out on content that matters to you

BT