# SQL for MongoDB

In MongoDB and Amazon DocumentDB, 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 or Amazon DocumentDB collections as well. IntelliJ IDEA will translate your SQL queries to JavaScript.

* To see the JavaScript version of SQL, right-click a query and select Show JS Script.

* The current implementation supports SELECT queries and the following сlauses: `JOIN`, `WHERE`, `GROUP BY`, `HAVING`, `ORDER BY`, `LIMIT`, `OFFSET`.

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

MongoDB:

![alt_translated_js_code](https://resources.jetbrains.com.cn/help/img/idea/2026.2/db_sql_for_mongodb_translated_javascript_code.png)

Amazon DocumentDB:

![alt_translated_js_code](https://resources.jetbrains.com.cn/help/img/idea/2026.2/db_sql_for_documentdb_translated_javascript_code.png)

## Data types

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

```SQL
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 (```).

```SQL
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. IntelliJ IDEA will add the `new` keyword to the MongoDB or Amazon DocumentDB 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, IntelliJ IDEA 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:

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

will be translated to:

```JAVASCRIPT
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 and Amazon DocumentDB 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 and Amazon DocumentDB, 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:

```SQL
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](https://docs.mongodb.com/manual/reference/operator/aggregation/).

### Limitations

* Due to different approaches to aggregate functions in SQL and MongoDB and Amazon DocumentDB, 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 and Amazon DocumentDB, 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 or Amazon DocumentDB. MongoDB: ![Wildcards for the LIKE operator](https://resources.jetbrains.com.cn/help/img/idea/2026.2/db_sql_for_mongodb_wildcards_for_the_like_operator.png) Amazon DocumentDB: ![Wildcards for the LIKE operator](https://resources.jetbrains.com.cn/help/img/idea/2026.2/db_sql_for_documentdb_wildcards_for_the_like_operator.png)

## 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. MongoDB: ![Embedded fields](https://resources.jetbrains.com.cn/help/img/idea/2026.2/db_sql_for_mongodb_embedded_fields.png) Amazon DocumentDB: ![Embedded fields](https://resources.jetbrains.com.cn/help/img/idea/2026.2/db_sql_for_documentdb_embedded_fields.png)

#### Limitations

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

### FROM

* You can use aliases for tables.

* You have to use an alias for a nested `SELECT` statement.

* You can use nested `SELECT` queries. For example, the following query is valid: MongoDB: ![Nested SELECT queries](https://resources.jetbrains.com.cn/help/img/idea/2026.2/db_sql_for_mongodb_nested_select_queries.png) Amazon DocumentDB: ![Nested SELECT queries](https://resources.jetbrains.com.cn/help/img/idea/2026.2/db_sql_for_documentdb_nested_select_queries.png)

#### 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: MongoDB: ![Only one condition in the ON part](https://resources.jetbrains.com.cn/help/img/idea/2026.2/db_sql_for_mongodb_one_condition_in_the_on_part.png) Amazon DocumentDB: ![Only one condition in the ON part](https://resources.jetbrains.com.cn/help/img/idea/2026.2/db_sql_for_documentdb_one_condition_in_the_on_part.png)

* 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: MongoDB: ![LIKE and NOT LIKE require a string literal](https://resources.jetbrains.com.cn/help/img/idea/2026.2/db_sql_for_mongodb_like_and_not_like_require_a_string_literal.png) Amazon DocumentDB: ![LIKE and NOT LIKE require a string literal](https://resources.jetbrains.com.cn/help/img/idea/2026.2/db_sql_for_documentdb_like_and_not_like_require_a_string_literal.png)

### GROUP BY

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

MongoDB:

![GROUP BY](https://resources.jetbrains.com.cn/help/img/idea/2026.2/db_sql_for_mongodb_group_by.png)

Amazon DocumentDB:

![GROUP BY](https://resources.jetbrains.com.cn/help/img/idea/2026.2/db_sql_for_documentddb_group_by.png)

### HAVING

IntelliJ IDEA supports the `HAVING` clause.

MongoDB:

![HAVING](https://resources.jetbrains.com.cn/help/img/idea/2026.2/db_sql_for_mongodb_having.png)

Amazon DocumentDB:

![HAVING](https://resources.jetbrains.com.cn/help/img/idea/2026.2/db_sql_for_documentdb_having.png)

### ORDER BY

IntelliJ IDEA supports the `ORDER BY` clause. Instead of `ORDER BY`, you can use `SORT BY`.

MongoDB:

![ORDER BY](https://resources.jetbrains.com.cn/help/img/idea/2026.2/db_sql_for_mongodb_order_by.png)

Amazon DocumentDB:

![ORDER BY](https://resources.jetbrains.com.cn/help/img/idea/2026.2/db_sql_for_documentdb_order_by.png)

### LIMIT

IntelliJ IDEA supports the `LIMIT` clause.

MongoDB:

![LIMIT](https://resources.jetbrains.com.cn/help/img/idea/2026.2/db_sql_for_mongodb_limit.png)

Amazon DocumentDB:

![LIMIT](https://resources.jetbrains.com.cn/help/img/idea/2026.2/db_sql_for_documentdb_limit.png)

### OFFSET

IntelliJ IDEA supports `OFFSET` clause.

MongoDB:

![OFFSET](https://resources.jetbrains.com.cn/help/img/idea/2026.2/db_sql_for_mongodb_offset.png)

Amazon DocumentDB:

![OFFSET](https://resources.jetbrains.com.cn/help/img/idea/2026.2/db_sql_for_documentdb_offset.png)