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. RubyMine 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
RubyMine 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.
You can use string literals in single or double quotation marks.
Write column names without quotation marks or use a grave accent (`
).
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. RubyMine 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, RubyMine 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:
will be translated to:
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:
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
RubyMine 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.
![GROUP BY GROUP BY](https://resources.jetbrains.com/help/img/idea/2021.3/db_mongodb_group_by.png)
HAVING
RubyMine supports the HAVING clause.
![HAVING HAVING](https://resources.jetbrains.com/help/img/idea/2021.3/db_mongodb_having.png)
ORDER BY
RubyMine supports the ORDER BY clause. Instead of ORDER BY, you can use SORT BY.
![ORDER BY ORDER BY](https://resources.jetbrains.com/help/img/idea/2021.3/db_mongodb_order_by.png)
LIMIT
RubyMine supports the LIMIT clause.
![LIMIT LIMIT](https://resources.jetbrains.com/help/img/idea/2021.3/db_mongodb_limit.png)
OFFSET
RubyMine supports OFFSET clause.
![OFFSET OFFSET](https://resources.jetbrains.com/help/img/idea/2021.3/db_mongodb_offset.png)