Introduction

MongoDB provides so many fantastic features, there can be a lot to learn when getting started.

A common frustration we see with newcomers, especially those already used to another DB query language such as SQL, is in picking up the particulars of the MongoDB query language.

For example, the newcomers must remember that an expression such as:

units >= 160

is expressed as:

{ 'units': { '$gte': 160 } }

in a MongoDB query.

We wanted to make it a little easier to get started with MongoDB and give such newcomers a helping hand, so we’ve launched SQL Query – a feature that makes it possible to query MongoDB with SQL.

Download our MongoDB GUI in case you haven’t yet, and let’s see SQL Query in action.

Getting Started – a Basic SELECT * Query

Let’s get started with a basic ‘SELECT *‘ query that simply retrieves all of the documents in a collection (similar to selecting all the rows of a table in an SQL database).

First, click on a collection, then click on ‘SQL’ in the toolbar. A basic ‘SELECT *‘ is automatically generated for us!

With Studio 3T's SQL Query feature, users can now query MongoDB with SQL

To see the results, simply execute the query by placing the cursor on the query and clicking the light blue execute button or using the F5 keyboard shortcut. The results are displayed below.

In-place editing is also available in SQL Query, a Studio 3T feature that lets you query MongoDB with SQL

Edit Results Inline

Editing documents and data in the SQL view in Studio 3T is based on a ‘search and edit’ paradigm.

That is, rather than have to search for the documents you want to edit and then issue separate UPDATE, DELETE or INSERT commands, you can simply edit the documents directly inline!

To edit a particular field or value in a document, simply double-click on it and a type-specific editor for that value will be activated.

Pressing ‘Enter’ will write the new value to the database, while pressing ‘Esc’ will return the previous value and exit the editor. You can find other useful shortcuts on our Studio 3T hotkeys list.

Read-Only Mode

If you would rather that results were not directly editable, say perhaps on a production database, you can enable a ‘Read-Only’ mode in which all editors are disable and data and documents cannot be edited inline.

Read-only mode can be activated dynamically by clicking on the padlock icon in the results view. Whether read-only mode is activated by default or not can be configured in Preferences.

Read-only mode is also available in SQL Query, a Studio 3T feature that lets you query MongoDB with SQL

Expressing Query Criteria

To do any real form of searches, we’ll most likely need to specify some search criteria. Studio 3T supports a wealth of SQL expressions, and we’ll illustrate these through examples.

In the examples that follow we’ll assume we have a collection called ‘housing‘ which contains documents that have a ‘zip_code‘ field and a ‘units‘ (the number of units available) field.

Projection

Let’s say we only want to see the ‘zip_code‘ and ‘units‘ fields in our documents. That is, we wish to perform a projection over ‘zip_code‘ and ‘units‘. We would write:

select zip_code, units
from housing;

Comparison

If we wanted to retrieve all documents in the collection that had the particular postcode ‘60606’, we would write:

select *
from housing
where zip_code = '60606';

We could also use any of the standard SQL comparison operators: =, <>, <, <=, >=, or >.

Combining Expressions

Expressions may be combined using, e.g. ‘and‘ or ‘or‘:

select *
from housing
where zip_code = '60606'
and units < 100;

Sorting

Results can be ordered or sorted by specifying an ‘order by‘ clause:

select *
from housing
order by units;

orders all documents in the ‘housing‘ collection by the ‘units‘ field in ascending order. Whereas:

select *
from housing
order by zip_code desc, units desc;

orders all documents in the ‘housing‘ collection first by the ‘zip_code‘ field in descending order, then by the ‘units‘ field in descending order.

Matching Boolean Values

If you are querying Boolean fields in a MongoDB document, use the Boolean values true and false to match against them, for example:

select *
from docsWithBoolsCollection
where myBoolField = true;

Some dialects of SQL use the integer values 1 and 0 to represent the Boolean values true and false respectively. But take note – in MongoDB integer and Boolean values are of distinct types.

MongoDB collections are schema-free, so there’s no schema to indicate that a particular field is of Boolean type and therefore a value of 1 really means true in that case.

If you attempt to match a field with a Boolean value against 0 or 1, no match will occur. With Studio 3T you don’t need to worry, just remember to use true and false when matching Boolean values.

We’ll cover more examples and supported expressions in ‘More Advanced Expressions’ below.

Converting SQL Queries to the MongoDB query language, JavaScript, Java, Python, and C#

When SQL Query was rolled out with Studio 3T 5.3.0, it was already possible to see how SQL queries translate to their equivalent MongoDB queries, which is a great way of learning the MongoDB query language.

But with Studio 3T 5.6.0, it’s now also possible to view how your SQL queries translate to JavaScript (node.JS), Java, Python and C# through another powerful Studio 3T feature called Query Code.

For all users, clicking on the ‘SQL Query’ tab will show the corresponding SQL of the query that has been executed:

SQL Query is a Studio 3T feature that lets you use SQL to query MongoDB

If you’re using the latest Studio 3T version (5.6 at the time of writing), you’ll see a tab called ‘Query Code’. Simply choose your target language in the dropdown to view your SQL query’s equivalent code in that language.

View your SQL query's equivalent code in the mongo shell language, JavaScript, Java, C#, and Python with Studio 3T

If you’re using an older version of Studio 3T, why not download the latest version? 🙂 But in the meantime, you can still click on the ‘MongoDB Query’ tab which will show the equivalent MongoDB query.

View your SQL query's equivalent MongoDB query through Studio 3T's SQL Query feature

 

Explain Data for the Query

You can also click on the ‘Explain’ tab and this will show expected performance data of executing the query. A full explanation of the explain data is beyond the scope of this tutorial, but please refer to the MongoDB manual for details.

The Explain tab on Studio 3T's SQL Query feature

More Advanced Expressions

The ‘Expressing Query Criteria’ section above provided enough examples to get us started with querying MongoDB using SQL. This section provides further examples that illustrate how we can use more advanced SQL expressions.

Limit and Offset

We can limit the number of documents returned in the result set by specifying a ‘limit‘ clause:

select *
from housing
limit 3;

will limit the size of the result set to 3 documents.

If we wish to also skip a number of documents in the result set, we can specify an ‘offset‘ clause:

select *
from housing
limit 3
offset 10;

will exclude / skip past the first 10 documents in the result set whilst limiting the size of the result set to 3 documents.

Wildcards

String values can be matched using ‘wildcards’. For example:

select *
from housing
where zip_code like '%606%';

will match all documents in the ‘housing‘ collection where the value of ‘zip_code‘ contains the substring ‘606’.

The following query:

select *
from housing
where zip_code like '606__';

will match all documents in the ‘housing‘ collection where the value of ‘zip_code‘ is 5 characters long, the first three of which are ‘606’.

Testing If a Value Is a Member of a Set

We can test if a value is a member of a set by using the ‘in‘ operator:

select *
from housing
where zip_code in ('60606', '60608', '60616');

Testing If a Value Lies within a Range

We can test if a value lies with a range by using the ‘between‘ operator:

select *
from housing
where zip_code between '60608' and '60616';

Accessing Embedded Fields Using Dotted Names

Some fields you may wish to access may be contained within an embedded document. You can access such fields using a dotted name.

For example, let’s assume one of the documents in the ‘housing‘ collection had a ‘community_area‘ field which was itself a document, containing a ‘name‘ and ‘number‘ field:

{
  community_area: {
    name: 'Lovelyville',
    number: 606
  },
  zip_code: '60606',
  units: 888
}

The ‘name‘ and ‘number‘ fields would be referenced as community_area.name and community_area.number respectively:

select *
from housing
where community_area.name = 'Lovelyville'
and community_area.number = 606;

Be aware however, of the differences when quoting names and string values. This is the subject of the next section.

Quoting Names and String Values

String values in an SQL query in Studio 3T can either be single- or double-quoted. For example, we could write (considering only part of the where clause from the example in the previous section):

where community_area.name = 'Lovelyville'

or:

where community_area.name = "Lovelyville"

where Lovelyville is a string value.

Names, including collection names and field names, both dotted and not, may be quoted using back-ticks, square brackets, or may not be quoted at all. For example, we could write:

where `community_area.name` = 'Lovelyville'

or:

where [community_area.name] = 'Lovelyville'

or:

where community_area.name = 'Lovelyville'

There may be some potential for confusion here, as some SQL dialects use double-quotes to quote names, rather than values. But fear not, when writing SQL queries in Studio 3T the rules are simple – use single- or double-quotes for string values, and use back-ticks, square brackets or no quotes at all for names.

Supported Date and Time Formats

Studio 3T allows date and times to expressed in a number of different formats. Although verbose, simply providing an example of each supported format is probably the easiest and clearest way of illustrating them. Here goes:

select *
from dates_example
where d > date('2017-03-22T00:00:00.000Z');

select *
from dates_example
where d > date('2017-03-22T00:00:00.000+0000');

select *
from dates_example
where d > date('2017-03-22T00:00:00.000');

select *
from dates_example
where d > date('2017-03-22T00:00:00');

select *
from dates_example
where d > date('2017-03-22T00:00');

select *
from dates_example
where d > date('2017-03-22 00:00:00.000Z');

select *
from dates_example
where d > date('2017-03-22 00:00:00.000+0000');

select *
from dates_example
where d > date('2017-03-22 00:00:00.000');

select *
from dates_example
where d > date('2017-03-22 00:00:00');

select *
from dates_example
where d > date('2017-03-22 00:00');

select *
from dates_example
where d > date('20170322T000000.000Z');

select *
from dates_example
where d > date('20170322T000000.000+0000');

select *
from dates_example
where d > date('20170322T000000.000');

select *
from dates_example
where d > date('20170322T000000');

select *
from dates_example
where d > date('20170322T0000');

select *
from dates_example
where d > date('2017-03-22');

select *
from dates_example
where d > date('20170322');

Querying Special BSON Data Types

MongoDB supports special BSON data types, which in the MongoDB shell are represented by ObjectId, NumberDecimal and BinData, for example. To query values of these types, simply write out the values as you would in the shell:

select *
from specialBSONDataTypesCollection
where _id = ObjectId("16f319f52bead12669d02abc");

select *
from specialBSONDataTypesCollection
where aNumberDecimal = NumberDecimal("9876543210987654321.0");

select *
from specialBSONDataTypesCollection
where aBinDataField = BinData(0, "QyHcug==");

ISODate values can also be queried this way, but as described in the section above, it can be more convenient to use the date function provided in the Studio 3T SQL tab to specify date values in various common, concise formats.


Thanks for reading our tutorial to the end! We hope that you’re now well-equipped to query MongoDB with SQL.

We hope you find a lot of use for the SQL Query feature, which now also supports Query Code, another handy Studio 3T feature that lets you translate your SQL queries to MongoDB queries, JavaScript (node.JS), Java, Python, and C#. Here’s the download link, once again.

We greatly welcome and appreciate all feedback, so just send us a message if there’s something you’re not clear on, or something you would like to see added or changed.

Editor’s Note: This post was updated in November 2017.