SQL for MongoDB

In MongoDB, to manipulate data or perform other operations, you write scripts for the mongo shell in JavaScript. For developers who query data primarily with SQL, it might be helpful to use SQL for MongoDB collections as well. DataSpell will translate your SQL queries to JavaScript.

See the JavaScript translation for an SQL query

Right-click a query and select Show JS Script. To copy JavaScript code to the clipboard, click Copy JS Script to Clipboard.
You can change and run the script from the JS Script Preview.

Data types

DataSpell supports the following data types:

String, Integer, Float, Boolean
NULL, NAN, INFINITY
Arrays ([1, 2, 3]) and maps ({a: 1, b: 2, c: 3})

Arrays and maps may include any expressions.

SELECT [1, 3 + 1 - 2, price] AS elements FROM sales;
SELECT {a: 1 + 2, b: NULL, c: FALSE, d: [1, 2]} FROM sales;

You can use string literals in single or double quotation marks.

Write column names without quotation marks or use a grave accent (`).

SELECT  _id,
        `acquisitions`,
        `category_code`,
        `description`,
        email_address,
        phone_number
FROM companies WHERE category_code = 'social'
AND description = "Mobile Dating";

Object constructors

You can use the following object constructors: BinData, HexData, UUID, MD5, ObjectID, Date, ISODate, Code, DBRef, RegExp, NumberInt, NumberLong, NumberDecimal, Timestamp, MinKey, MaxKey.

You may use or skip the new keyword. DataSpell will add the new keyword to the MongoDB query automatically. This feature does not apply to the Date object constructor because of the difference between Date() new Date(). Date() returns the current date as a string and new Date() as a Date object.

If the function name is unknown, DataSpell searches for the new keyword before the function name. If the new keyword is present, the name is treated as an object constructor. Otherwise, as a function call. For example, the following query:

SELECT  new UnknownTypeConstructor() AS A,
        new NumberInt() AS B,
        NumberInt(1) AS C FROM companies;

will be translated to:

db.getSiblingDB("admin").getCollection("companies").aggregate([
  {
    $project: {"A": {$literal: new UnknownTypeConstructor()},
               "B": {$literal: new NumberInt()},
               "C": {$literal: new NumberInt(1)},
               "_id": 0}
  }
])

Functions

You can use all the available MongoDB aggregation pipeline operators except for map, reduce, filter, and let. Also, you can use COUNT(*) but not COUNT (expression) that is not supported yet.

In MongoDB, some functions require named arguments. To use named arguments, use a PostgreSQL-like syntax for the named parameters (for example, => or :=). You will get an error if you do not specify names for the parametrized functions. Consider the following example for the dateToString function:

SELECT dateToString(date => ISODate(),
                    format := '%H:%M:%S:%L%z',
                    timezone => 'Europe/London') AS 'Date'
FROM companies;

You can see the list of all the pipeline operators that support named parameters in Aggregation Pipeline Operators at docs.mongodb.com.

Limitations

Due to different approaches to aggregate functions in SQL and MongoDB, you cannot use aggregate functions, such as AVG, SUM, MIN, and MAX, as non-aggregate. For example, SELECT MAX(1,2,3) will not work.
In MongoDB, functions like AVG, SUM, MIN, and MAX may be aggregate and non-aggregate. They are aggregate if they are placed in the group block. Otherwise, they are non-aggregate.
In SQL, there is no such dependency, and you can use aggregate functions without the GROUP BY clause (for example, SELECT AVG(x) FROM t).
You cannot call aggregate functions within other aggregate functions.

Operators

Currently, the following functionality is supported:

Operands: =, ==, >=, <=, >, <, <>, !=, +, -, /, *, %, AND, OR, NOT, LIKE, NOT LIKE, IS,IS NOT, IN, NOT IN, BETWEEN, NOT BETWEEN.
You can use wildcards for the LIKE operator. These wildcards are translated into a valid regular expression for MongoDB.

SQL clauses

SELECT

The following features are supported for the SELECT queries.

Options: DISTINCT, ALL.
Aggregate and non-aggregate functions.
Access to embedded fields by using dotted names. Consider the following example.

Limitations

Columns may have aliases. Aliases must not contain periods(.).

FROM

You can use aliases for tables.
You must use an alias for a nested SELECT statement.
You can use nested SELECT queries. For example, the following query is valid:

Limitations

Aliases must not contain periods(.).

JOIN

Only JOIN (INNER JOIN) and LEFT JOIN (LEFT OUTER JOIN) are supported.
Only the ON condition is supported.
Only one condition in the ON part. The following query will result in error:
You can use == or = operators in the ON condition.
Multiple JOIN clauses are supported.

Limitations

USING is not supported.
You cannot use a SELECT statement as the second argument in JOIN clauses.
Table names and aliases must not have duplicates in JOIN clauses.

WHERE

LIKE and NOT LIKE require a string literal. The following query results in error:

GROUP BY

DataSpell supports the GROUP BY clause.

The SELECT clause may include expressions that functionally depend on expressions in the GROUP BY clause. You can use embedded fields of a column in the SELECT clause if the column is used in the GROUP BY clause.

HAVING

DataSpell supports the HAVING clause.

ORDER BY

DataSpell supports the ORDER BY clause. Instead of ORDER BY, you can use SORT BY.

LIMIT

DataSpell supports the LIMIT clause.

OFFSET

DataSpell supports OFFSET clause.

Last modified: 14 December 2021