BT

InfoQ Homepage News Studio 3T: SQL Exploration for MongoDB

Studio 3T: SQL Exploration for MongoDB

Bookmarks

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
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.

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

Community comments

  • Interesting but

    by Damien Pal /

    Your message is awaiting moderation. Thank you for participating in the discussion.

    ... 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 /

    Your message is awaiting moderation. Thank you for participating in the discussion.

    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 /

    Your message is awaiting moderation. Thank you for participating in the discussion.

    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 /

    Your message is awaiting moderation. Thank you for participating in the discussion.

    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 /

    Your message is awaiting moderation. Thank you for participating in the discussion.

    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

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

BT

Is your profile up-to-date? Please take a moment to review and update.

Note: If updating/changing your email, a validation request will be sent

Company name:
Company role:
Company size:
Country/Zone:
State/Province/Region:
You will be sent an email to validate the new email address. This pop-up will close itself in a few moments.