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.

Download Studio 3T
Get Download Link for Later

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.

Joins

The 2018.3 release of Studio 3T introduces support for joins. In MongoDB, joins are natively supported via the $lookup operator in aggregation queries. Building on this, SQL Query in Studio 3T supports inner joins and left joins, familiar to SQL users.

Read our latest MongoDB tutorial on how to create MongoDB joins using SQL.

Rather than provide a tutorial on SQL joins (available elsewhere), we’ll focus on the specifics of inner joins and left joins as supported by Studio 3T. Any SQL join query run in Studio 3T is converted into a MongoDB aggregate query. This has the fantastic advantage that you can then run join queries (via it’s aggregate query equivalent) on any appropriate MongoDB database, without any special support or libraries, etc. – it’s all simply plain MongoDB! However, in doing so, there are one or two considerations you must bear in mind, which we will cover below.

Inner Joins and Left Joins

To start, the syntax is simple. To perform an inner join, you can write queries of the form:

select *
from collA
inner join collB on collA.field1 = collB.field2;

And for left joins:

select *
from collA
left join collB on collA.field1 = collB.field2;

Projections

Projections can be performed on the joined collections. These take the following form:

select collA.field1, collA.field3, collB.field2, collB.field4
from collA
inner join collB on collA.field1 = collB.field2;

Note that fields referenced in the projection must be qualified with the collection name, e.g. ‘collA.field1‘ and not just ‘field1‘. While in a relational ‘schemaful’ setting, providing only the column (field) name would be sufficient if it were distinct to one of the tables, in the schemaless MongoDB setting, there’s no schema to indicate to which collection a particular field belongs, so we must qualify the field name explicitly with it’s collection. By the same token, note that while wildcards such as ‘select * ...‘ are permitted, ‘select collA.* ...‘ are not.

Multiple Joins

Multiple joins are supported, you would simply write queries such as:

select *
from collA
inner join collB on collA.field1 = collB.field2
left join collC on collB.field3 = collC.field1;

The order in which joins are processed is the same in which they are written, and a join condition must only reference collections to the left of it.

Extended Queries

And of course, SQL aggregate functions, group by, having, etc. can all be applied to the joined collections too, e.g. you could write:

select collA.field3, collB.field3, count(*)
from collA
inner join collB on collA.field1 = collB.field2
where collA.date > date('2018-01-01')
group by collA.field3, collB.field3
having count(*) > 250
order by collA.field3, collB.field3
limit 1
offset 1;

Joining a Collection to Itself

A collection can be joined to itself through the use of aliases, e.g. you could write:

select *
from collA as child
inner join collA as parent on parent._id = child.parentId;

Note that after a collection has been aliased, all references to its fields must use that alias and not the original name.

Cross Joins

Should you need them, cross joins are also supported, e.g. you can write:

select *
from collA
cross join collB;

or:

select *
from collA, collB;

Take care however, cross join queries can quickly become expensive to run as the number of documents in the collections grow.

Now you can join in the fun!

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

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.

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.

And with Studio 3T 2018.2, we’ve added Query Code support for SQL ‘GROUP BY’ and aggregation queries, so you can translate them to the five languages too. Jump straight to the GROUP BY, ORDER BY and Aggregate Functions chapter for more information.

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

GROUP BY, ORDER BY and Aggregate Functions

GROUP BY, ORDER BY and aggregate functions are supported, making it possible to write queries such as:

select count(*), max(units), min(units), sum(units), zip_code
from housing
group by zip_code
order by max(units) desc;

The aggregate functions currently supported are COUNT, MIN, MAX, SUM and AVG.

With Studio 3T 2018.2, we’ve also added Query Code support for SQL ‘GROUP BY’ and aggregation queries, so you can translate them automatically to JavaScript, Java, Python, C#, and the mongo shell language.

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 are single-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'

where Lovelyville is a string value.

Names, including collection names and field names, both dotted and not, may be quoted using double-quotes, 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'

or:

where community_area.name = 'Lovelyville'

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

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.

Try SQL Query
Get Download Link for Later

Editor’s Note: This post was originally published in May 2017.