> This page location: /docs.md > /docs/tools-and-connectors.md > /docs/sql-interface.md

> For the complete MongoDB documentation index, see www.mongodb.com/docs/llms.txt

# MongoSQL Language Reference

This page outlines the syntax and semantics of MongoSQL, a SQL dialect that the SQL Interface uses to retrieve SQL from users or tools to then translate those statements into MQL (MongoDB Query Language). This page lists and describes supported clauses, operators, expressions, and functions.

## Compatibility and Limitations

- MongoSQL is based on the SQL-92 standard. However MongoSQL is not fully SQL-92 compatible due to the following limitations:

  - The `date` data type is not supported. Use `timestamp` instead.

  - Interval and date interval arithmetic are not supported.

- MongoSQL does not support MongoDB Vector Search and MongoDB Search.

## Type System

### Data Types

MongoSQL data types are the set of [BSON types](https://bsonspec.org/spec.html). All of these types can be queried in MongoSQL. They are:

- String (`STRING`)

- Document (`DOCUMENT`)

- Array (`ARRAY`)

- BinData (`BINDATA`)

- ObjectId (`OBJECTID`)

- Boolean (`BOOL`)

- Date (`TIMESTAMP`)

- Null (`NULL`)

- Regex (`REGEX`)

- 32-bit integer (`INT`)

- Double (`DOUBLE`)

- Long (`LONG`)

- Timestamp (`BSON_TIMESTAMP`)

- Decimal (`DECIMAL`)

- MinKey (`MINKEY`)

- MaxKey (`MAXKEY`)

- DBPointer (`DBPOINTER`)

- Symbol (`SYMBOL`)

- Javascript with scope (`JAVASCRIPTWITHSCOPE`)

- JavaScript (`JAVASCRIPT`)

Each type in MongoSQL has a name (in parentheses above), which is a keyword that can be used to reference the type when necessary, such as in an expression like `CAST`.

### Type Conversions

Explicit type conversions are expressed via the `CAST` function or the `::` operator. Numeric types are all mutually comparable; MongoSQL allows operations between the various numeric types without casting the operands to be the same numeric type.

### Schemas

MongoSQL converts MongoDB's flexible document values into types using a schema. An MongoSQL [schema](https://www.mongodb.com/docs/sql-interface/schema-management.md#std-label-sql-schema-management) is a collection of facts about an expression or collection that are known to be true at compile time.

For example, an MongoSQL schema might dictate that an expression is either a boolean or a document with subfields, or that an expression is either an array with a length of one or a positive integer.

If a static type constraint is not satisfied, the query will fail to compile.

Schema management differs between the On-Premise manual tool and Atlas automated tool.

## Clauses

MongoSQL queries support a basic set of SQL clauses. The available clauses are:

`SELECT``FROM``WHERE``GROUP BY``HAVING``ORDER BY``OFFSET``LIMIT`

### SELECT

`SELECT` begins every Atlas SQL query. MongoSQL allows `SELECT VALUE` and `SELECT VALUES` to be used interchangeably.

MongoSQL requires nested `SELECT` statements to have an alias.

```none
SELECT foo FROM (SELECT bar FROM baz) as subSelect
```

### SELECT DISTINCT

Use `SELECT DISTINCT` to exclude duplicate rows from the result set. Duplicate checking follows MongoDB equality semantics, where field order matters for document comparison and both element order and value matter for array comparison.

```none
SELECT DISTINCT bar FROM baz
```

#### CAST()

MongoSQL supports the `CAST()` function, which allows you to dynamically convert values in your query to a given data type.

```none
SELECT * FROM table WHERE period_start_utc >= CAST('2023-01-01T00:00:00.000Z' AS TIMESTAMP)
```

### FROM

`FROM` is the first clause evaluated in every MongoSQL query.

`FROM` can pull data from various sources including collections `(SELECT * FROM foo)`, arrays `(SELECT * FROM [{'a': 1}])`, joins `(SELECT * FROM a JOIN b)`, derived tables `(SELECT * FROM (SELECT a FROM foo) d)`, and [FLATTEN and UNWIND.](https://www.mongodb.com/docs/sql-interface/query-with-asql-statements.md#std-label-asql-flatten-and-unwind)

### WHERE

The `WHERE` clause is a filter on incoming data. Its expression must statically have type `BOOL` or `NULL` and may evaluate to `MISSING`.

### GROUP BY

`GROUP BY` provides a means for grouping and aggregating data.

`GROUP BY` keys can be top-level fields or subfields. If you group by subfields, they must be aliased in either the SELECT or GROUP BY clause. For example, in a `Sales` collection, you have documents with the following structure:

```none
{"customer": {"age": <Integer>}}
```

In this example, the following queries can be executed to use the `age` subfield as a group key:

```none
"SELECT customerAge FROM Sales GROUP BY customer.age AS customerAge"
"SELECT customer.age as customerAge FROM Sales GROUP BY customerAge"
```

Even if you [FLATTEN](https://www.mongodb.com/docs/sql-interface/query-with-asql-statements.md#std-label-asql-flatten-and-unwind) your data, former subfields must be aliased. For example, in an `Orders` collection, you have documents with the following structure:

```none
{"customer": {"address": {"city": <String>}}}
```

After flattening, the structure becomes `{"customer_address_city": <String>}`, and you can run the following queries to use the `city` subfield as a group key:

```none
"SELECT city FROM FLATTEN(orders) GROUP BY customer_address_city AS city"
"SELECT customer_address_city AS city FROM FLATTEN(orders) GROUP BY city"
```

#### Aggregation Functions

MongoSQL supports the following aggregation functions.

| Name | Description | Notes |
| --- | --- | --- |
| `ADD_TO_ARRAY` | Pushes the argument to the end of an array. The total output of this function will be an array. | The argument to `ADD_TO_ARRAY` can have any type. |
| `ADD_TO_SET` | Pushes the argument to the end of an array removing duplicates. The total output of this function will be an array with all duplicate items removed. Duplicates are determined using the `=` operator. | The argument to `ADD_TO_SET` can have any type. |
| `AVG` | Returns the average of all the arguments. | The argument must be statically typed to a numeric type. |
| `COUNT` | Counts the number of elements. `COUNT(*)` counts all values unconditionally. `COUNT(<expression>)` counts all values for which the expression does not result in `NULL` or `MISSING`. | The argument to `COUNT` can have any type. |
| `FIRST` | Returns the first element in the group. | Deterministic only when the input has deterministic order, otherwise undefined. |
| `LAST` | Returns the first element in the group. Deterministic only when the input has deterministic order, otherwise undefined. | The argument to `LAST` can have any type. |
| `MAX` | Returns the maximum element as ordered by the MongoSQL `>` operator. | The argument must be statically typed to be comparable via the `>` operator. |
| `MERGE_DOCUMENTS` | Returns a document formed by successively merging documents, with the previous element used as the left hand side. In the case of duplicate keys, the value of the key in the new element is kept. As with `FIRST` and `LAST`, the output is only deterministic when the input has deterministic ordering. | The argument must be statically typed as `DOCUMENT`. |
| `MIN` | Returns the minimum element as ordered by the MongoSQL `<` operator. | The argument must be statically typed to be comparable via the `<` operator. |
| `STDDEV_POP` | Returns the standard deviation of all elements over the entire group population. | The argument must be statically typed to a numeric type. See [stdDevPop.](https://www.mongodb.com/docs/manual/reference/operator/aggregation/stdDevPop/) |
| `STDDEV_SAMP` | Returns the standard deviation of a sample of all elements in the group. See [stdDevPop.](https://www.mongodb.com/docs/manual/reference/operator/aggregation/stdDevSamp/) | The argument must be statically typed to a numeric type. |
| `SUM` | Returns the sum of all the arguments. | The argument must be statically typed to a numeric type. |

### HAVING

The `HAVING` clause operates in the same way as a `WHERE` clause, but after the `GROUP BY` clause. Like the `WHERE` clause, the `HAVING` clause takes an expression that must statically have type `BOOL` or `NULL` and may evaluate to `MISSING`. It can reference aliases defined in the `GROUP BY` and can contain expressions with aggregation functions. Only aliases defined in the `GROUP BY` are available to the `HAVING` clause.

### ORDER BY

The `ORDER BY` clause provides a way to order a result set by one or more sort keys. Each sort key can be either a column reference, or an integer literal referring to a `SELECT` expression by its position in the select expression list. Sort keys that are column references can be compound identifiers. These compound identifiers can be qualified with datasource names or refer to document subfields.

MongoSQL sorts `MISSING` before `NULL`, and `NULL` before all other values. The `ORDER BY` clause requires that all possible values in a sort key expression can be statically verified to be comparable via the `>` (greater than)  and `<` (less than) operators.

### LIMIT and OFFSET

The `LIMIT` and `OFFSET` clauses allow users to retrieve only some of the rows returned by a query. If a `LIMIT` number is provided, no more than that number of rows will be returned. If an `OFFSET` number is provided, that number of rows is skipped before returning rows.

Both `LIMIT` and `OFFSET` numbers must be positive integers. Using `LIMIT` or `OFFSET` without `ORDER BY` does not guarantee the same result.

When `LIMIT` and `OFFSET` are both set, the `OFFSET` rows will be skipped before returning the rest of the results, which should contain no more than the `LIMIT` number rows.

`LIMIT i, j` is a shorter form of `LIMIT i OFFSET j`.

`LIMIT` and `OFFSET` can be used in subqueries.

## Set operations

The `UNION` and `UNION ALL` set operators return a single result set for two `SELECT` queries. The `UNION` operator removes duplicate rows from the result set, while the `UNION ALL` operator doesn't remove duplicate rows from the result set. The result set returned by these operators doesn't have a defined order.

MongoSQL does not support `INTERSECT` or `EXCEPT` set operations.

## Expressions

### Identifiers

Identifiers in MongoSQL refer to databases, tables, and columns. MongoSQL identifiers support all UTF-8 characters except the null character `\x00`.

In MongoSQL, some identifiers are restricted to avoid conflicts with characters that have other semantic meaning; for an identifier to include such a character, it must be *delimited*, that is, surrounded by double quotes or backticks. For example, an identifier must be delimited if it begins with a digit or if it conflicts with a reserved keyword (e.g. `"10cent"`). Identifiers are case-sensitive, whether delimited or not.

### Aliases

Identifiers are used for all aliases in MongoSQL. In most cases, MongoSQL returns an error if an alias is used more than once in the same clause. The exception to this is that aliases can be repeated on both sides of a `UNION ALL`. This also applies to automatically generated aliases.

### Keywords

MongoSQL keywords (such as `SELECT`, `FROM`, `JOIN`, etc.) cannot be used as undelimited identifiers.

### Literals

MongoSQL supports literals for booleans, null, numbers, and strings. Strings are enclosed in single quotes. To include a single quote character in a string literal, double it (`'o''clock'`).

Literal integers are typed as `INT` when within the 32-bit signed integer range and `LONG` otherwise. Literal floating point numbers or scientific notation numbers have type `DOUBLE`.

**Note:**

MongoSQL supports implicit type conversions of extended JSON-encoded strings into their corresponding type. As MongoSQL supports all BSON types, and all BSON types can be represented as extended JSON, you can include literal values of any type in a query. That is, you can include an extended JSON string value anywhere an expression is expected and MongoSQL automatically converts to the corresponding literal type. For example, `SELECT
    '{"$numberInt": "1"}' + 2 FROM foo` interprets as `SELECT 1 + 2 FROM foo`. This is particularly useful for datetime literals. For example, `SELECT * FROM
    foo WHERE myDate > '{"$date": "1995-06-28T03:05:00.000Z"}'`.

We recommend that you use implicit extended `JSON` instead of the `CAST()` function (or its shorthand operator, `::`) for writing literal values. For example, we don't recommend `CAST('1995-06-28T03:05:00.000Z' AS TIMESTAMP)` to include a literal datetime value in a query. Using `CAST()` or `::` is valid and works, but might have negative performance implications, especially when used in a `WHERE` clause. Extended JSON implicit conversion doesn't have any negative performance impact.

We recommend that you use `CAST()` in a `WHERE` clause when you want to ensure type safety and avoid ambiguity.

### Parenthesized Expressions

A parenthesized expression is an expression grouped by parentheses. Any time infix operators are present, the need for parentheses (or a similar mechanism) to distinguish order of operations may be necessary. MongoSQL has several infix operators, such as `+` and `::`. For example, the value of `1 + 2 * 3` is 7, while the value of `(1 + 2) * 3` is 9.

### Operators

MongoSQL supports the following basic operators:

- `+`

- `-`

- `*`

- `/`

- `||`

- `<`

- `<=`

- `!=`

- `==`

- `>`

- `>=`

- `BETWEEN`

- `AND`

- `OR`

- `NOT`

### Subquery Expressions

A subquery is a SQL query within a query. You can use a subquery anywhere an expression can be used.

MongoSQL supports scalar subquery and table subquery. A scalar subquery returns a result set with zero or one row and one column. It can be used in most places a literal or single column value is valid. A table subquery returns zero or more rows and one or more columns.

### Document and Field-Access Expressions

Documents can be represented with a syntax similar to JSON objects. Keys must be strings and values can have any of the [supported types](https://www.mongodb.com/docs/sql-interface/language-reference.md#std-label-asql-data-types). To access document fields, MongoSQL supports two options: "dot" notation and "bracket" notation.

Dot notation is similar to field access in MongoDB aggregation. For example, if a document `doc` contains a field `f`, then the expression `doc.f` is used to access the value of that field. Bracket notation uses square brackets (`[` and `]`) around a field name to access the field with that name. For example, consider the same document described before: `doc["f"]` is used to access the value of that field.

### Null and Missing

BSON distinguishes between `NULL` and `MISSING`. In the case of `NULL` there is a field with the literal value `NULL`, whereas in the case of `MISSING`, the field is gone.

## Array Functions

Starting in MongoSQL 2.0.0, array functions let you operate on array values natively in your queries. The `MAP`, `FILTER`, and `REDUCE` functions correspond to the [$map](https://www.mongodb.com/docs/manual/reference/operator/aggregation/map/), [$filter](https://www.mongodb.com/docs/manual/reference/operator/aggregation/filter/), and [$reduce](https://www.mongodb.com/docs/manual/reference/operator/aggregation/reduce/) aggregation pipeline operators. MongoSQL also supports utility functions that cover common uses of these three functions.

Like the corresponding aggregation pipeline operators, these functions use the `"this"` variable to refer to the array element that they process. `REDUCE` also uses the `"value"` variable to refer to the accumulated value. MongoSQL doesn't support custom names for these variables. Use `"this"` and `"value"`.

### MAP

`MAP(array, function)` applies a function to each element in an array and returns the transformed array. `MAP` takes the following arguments:

- Array expression

- Expression that specifies how to transform each element of the array

To refer to the element that `MAP` processes, the second argument must use the `"this"` variable. If the array is empty, `MAP` returns an empty array.

`MAP` can return an `ARRAY` or `NULL`. The following requirements apply to the arguments:

- The first argument must statically have type `ARRAY` or `NULL`, and might evaluate to `MISSING`. If the first argument is `NULL` or `MISSING`, `MAP` returns `NULL`. The type of the array elements must satisfy the uses of `"this"` in the second argument.

- The second argument can be any expression that is statically semantically valid. The type that the second argument returns is the type of the elements in the array that `MAP` returns.

The following query multiplies each element of the array by `2`:

```sql
SELECT MAP([1, 2, 3], "this" * 2) AS doubled_values
```

**Output:**

```none
{ '': { 'doubled_values': [2, 4, 6] } }
```

### FILTER

`FILTER(array, function)` applies a condition to each element in an array and returns an array that contains only the elements for which the condition evaluates to `true`, in their original order. `FILTER` takes the following arguments:

- Array expression

- Expression that specifies the condition to test against each element of the array

To refer to the element that `FILTER` processes, the second argument must use the `"this"` variable. If the array is empty, `FILTER` returns an empty array.

`FILTER` can return an `ARRAY` or `NULL`. Because `FILTER` doesn't transform elements, the elements in the array that `FILTER` returns have the same type as the elements in the input array. The following requirements apply to the arguments:

- The first argument must statically have type `ARRAY` or `NULL`, and might evaluate to `MISSING`. If the first argument is `NULL` or `MISSING`, `FILTER` returns `NULL`. The type of the array elements must satisfy the uses of `"this"` in the second argument.

- The second argument can be any expression that is statically semantically valid and that returns `BOOL` or `NULL`.

The following query retains only the odd-valued elements of the array:

```sql
SELECT FILTER([1, 2, 3], MOD("this", 2) = 1) AS odd_values
```

**Output:**

```none
{ '': { 'odd_values': [1, 3] } }
```

### REDUCE

`REDUCE(array, initialValue, function)` applies a function to each element in an array and combines the elements into a single value, starting with an initial value. `REDUCE` takes the following arguments:

- Array expression

- Initial value expression

- Expression that specifies how to combine each element of the array with the accumulated result of the previous elements

The accumulated result starts as the initial value. To refer to the element that `REDUCE` processes, the third argument must use the `"this"` variable. To refer to the accumulated value, the third argument must use the `"value"` variable. If the array is empty, `REDUCE` returns the initial value.

`REDUCE` returns a value of the same type that the third argument returns, a value of the type of the initial value, or both. This can be any MongoSQL type, including `NULL`. The following requirements apply to the arguments:

- The first argument must statically have type `ARRAY` or `NULL`, and might evaluate to `MISSING`. If the first argument is `NULL` or `MISSING`, `REDUCE` returns `NULL`. The type of the array elements must satisfy the uses of `"this"` in the third argument.

- The second argument must satisfy the uses of `"value"` in the third argument.

- The third argument can be any expression that is statically semantically valid and that satisfies its own uses of `"value"`. The type that the third argument returns must be the same as, a superset of, or a subset of the second argument. The two types can differ if MongoSQL detects no schema conflicts between the uses of `"value"` in the third argument and the type of the second argument.

The following query adds the elements of the array together, starting with `0`:

```sql
SELECT REDUCE([1, 2, 3], 0, "value" + "this") AS summed_value
```

**Output:**

```none
{ '': { 'summed_value': 6 } }
```

### Named Function Arguments

Instead of an expression, you can pass a named function as the final argument to `MAP`, `FILTER`, and `REDUCE`. Named functions are shorthand for expressions that use a single built-in function or operator. For example, instead of `MAP("array", LOWER("this"))`, you can write `MAP("array", LOWER)`. Instead of `REDUCE("array",
0, "value" + "this")`, you can write `REDUCE("array", 0, +)`.

You can use a named function argument with any unary or binary operator and with any scalar function that accepts one or two arguments.

### Additional Array Functions

MongoSQL supports the following array functions, which are syntactic aliases for common uses of `MAP`, `FILTER`, and `REDUCE`:

| Name | Description | Example |
| --- | --- | --- |
| `ARRAY_CAST` | Casts all elements of the array to the target type. | `ARRAY_CAST(['1', '2', '3'], INT)` returns `[1, 2, 3]`. |
| `ARRAY_EXTRACT` | Extracts the value at a field path from each element of the array. | `ARRAY_EXTRACT([{'a': 1, 'b': 10}, {'a': 2, 'b': '20'}], a)` returns `[1, 2]`. |
| `ARRAY_COMPACT` | Removes `NULL` and `MISSING` values from the array. | `ARRAY_COMPACT([1, NULL, 2, NULL, 3])` returns `[1, 2, 3]`. |
| `ARRAY_REMOVE` | Removes all instances of a value from the array. | `ARRAY_REMOVE([1, 2, 2, 3, 2], 2)` returns `[1, 3]`. |
| `ARRAY_COUNT_IF` | Returns the number of elements in the array that match a predicate. The predicate refers to the element that it tests with the `"this"` variable. | `ARRAY_COUNT_IF([1, 2, 3, 4, 5], "this" > 3)` returns `2`. |
| `ARRAY_SUM` | Returns the sum of an array of numbers. | `ARRAY_SUM([1, 2, 3])` returns `6`. |
| `ARRAY_PRODUCT` | Returns the product of an array of numbers. | `ARRAY_PRODUCT([4, 5, 6])` returns `120`. |
| `ARRAY_AVG` | Returns the average of an array of numbers. | `ARRAY_AVG([1, 2, 3])` returns `2`. |
| `ARRAY_ALL` | Returns the conjunction of an array of booleans. | `ARRAY_ALL([true, false, true])` returns `false`. |
| `ARRAY_ANY` | Returns the disjunction of an array of booleans. | `ARRAY_ANY([false, false, true])` returns `true`. |
| `ARRAY_JOIN` | Returns the concatenation of an array of strings. The separator argument is optional and defaults to an empty string. | `ARRAY_JOIN(['a', 'b', 'c'])` returns `'abc'`. `ARRAY_JOIN(['a', 'b', 'c'], '-')` returns `'a-b-c'`. |

## Comments

Comments are sequences of characters within queries that do not impact query execution. MongoSQL supports both standard SQL comments and C-style block comments.

Standard SQL comments begin with double dashes and end with a new line:

```text
\-- This is a standard SQL comment
```

Block comments begin with `/*` and end at the matching occurrence of `*/`.

```text
/* This is a
multiline comment
*/
```
