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

A common frustration we see with newcomers, especially with those who are already familiar with another DB query language such as SQL, is in picking up the particulars of the MongoDB query language. e.g. the newcomers must remember that an expression such as:

units >= 160

is expressed as:

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

in a MongoDB query.

At Studio 3T (formerly MongoChef), we wanted to make it a little easier to get started with MongoDB and give such newcomers a helping hand up – with Studio 3T, you can query MongoDB with SQL.

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!

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.

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.

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.

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.

View the Equivalent MongoDB Query

In Studio 3T we can view not only the SQL of the query that has been executed, but also the equivalent MongoDB query!

Clicking on the ‘SQL Query’ tab will show the corresponding SQL of the query that has been executed:

While clicking on the ‘MongoDB Query’ tab will show the equivalent MongoDB query:

A great way to learn the MongoDB query language!

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.

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 the tutorial to the end. We hope you find a lot of use for the SQL query feature in Studio 3T and that it gives you a hand up in getting started with MongoDB. You can also check out this video here for more information on using SQL to query.

The SQL query feature is available in Studio 3T’s Pro and Enterprise editions. Try it for 14 days, and let us know what you think! 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.

For now, please feel free to read up on Studio 3T’s other great features like how to do aggregate and map-reduce queries, to complement this tutorial.