DataGrip 2020.3 EAP 3: SQL for MongoDB

Maksim Sobolevskiy

Hello! In this post about our third EAP build for 2020.3, we’ll describe just one feature – but it’s a huge one:

SQL for MongoDB

TL;DR – You can now use SQL to query MongoDB databases.

Only SELECT queries work.

Working сlauses: JOIN, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT, OFFSET.

SELECT

• Options you can use: DISTINCT, ALL.
• You can use aggregate functions: AVG, SUM, MIN, MAX, and others.
• Columns can be qualified.

Restrictions

• COUNT (expression) is not yet supported.
• You can’t use aggregate functions, such as AVG, SUM, MIN, and MAX, as if they were not aggregate. For example, SELECT MAX(1,2,3) won’t work.

FROM

• Nested SELECT statements are supported.
• You can use aliases for tables.
• You must use an alias for a nested SELECT statement. An inspection will display a warning.

Restriction

• Aliases must not contain dots.

JOIN

• Only JOIN (INNER JOIN) and LEFT JOIN (LEFT OUTER JOIN) are supported.
• Only the ON condition is supported. USING doesn’t work.
• You can only use one condition at a time.
• You can only use the `=` operator in the ON condition. `==` also works.

Restrictions

• Nested SELECTs are not supported in JOIN clauses.
• Table names and aliases shouldn’t be duplicated in all JOIN clauses.

WHERE

• Supported operands: =, ==, >=, <=, >, <, <>, !=, +, -, /, *, %, AND, OR, NOT, LIKE, NOT LIKE, IS, IS NOT, IN, NOT IN, BETWEEN, NOT BETWEEN.
• LIKE requires a string literal. You can use the classic wildcards for the LIKE operator; they will be translated into the valid regular expression for MongoDB.

GROUP BY, HAVING, ORDER BY

• You can use aggregate functions: AVG, SUM, MIN, MAX, and others.

Data types

You can use:

• String, Integer, Float, Boolean
• NULL, NAN, INFINITY
• Arrays and maps

Functions

You can use all available MongDB functions except map, reduce, filter, and let.

Some functions in MongoDB, such as dateToString, require named arguments. To use them, please use a PostgreSQL-like syntax for the named parameters, using ‘=>’ or ‘:=’. You’ll get an error if you don’t specify names for the parametrized functions, and vice versa.

Object builders

The following object builders can all be used: BinData, HexData, UUID, MD5, ObjectID, Date, ISODate, Code, DBRef, RegExp, NumberInt, NumberLong, NumberDecimal, Timestamp, MinKey, MaxKey.

Regardless of whether you use them with the new keyword, we’ll add it to the actual MongoDB query. The only time we won’t do so is when you use the Date object builder, because in this case it matters.

If we don’t know whether you are using a function or an object builder, we consider the usage to be a function call if the new keyword is not present, and we take it to be an object builder if the new keyword is present.

Behind the scenes

Want to know what will be sent to the MongoDB database? There are two options in the context menu: Copy JS script to clipboard and Show JS Script. In the JS Script Preview window you can edit the query and run it.

That’s it! We are looking forward to receiving your feedback, and we hope our 2020.3 release will help you work with MongoDB!

Subscribe to DataGrip Blog updates

Subscribe form

By submitting this form, I agree to the JetBrains Privacy Policy Notification icon

By submitting this form, I agree that JetBrains s.r.o. ("JetBrains") may use my name, email address, and location data to send me newsletters, including commercial communications, and to process my personal data for this purpose. I agree that JetBrains may process said data using third-party services for this purpose in accordance with the JetBrains Privacy Policy. I understand that I can revoke this consent at any time in my profile. In addition, an unsubscribe link is included in each email.

Submit

Thanks, we've got you!