DataGrip 2020.3 EAP 3: SQL for MongoDB

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!

Discover more

DataGrip 2021.1.2

Hello! Today we’ve got a new minor update for DataGrip 2021.1. Let's see what’s been improved. Features DBE-13048: Some run configurations require the data source to be disconnected first. Now it’s possible to disconnect from a data source before launch. DBE-12906: We’ve introduced a Modify grants action. Introspection DBE-6276: [PostgreSQL] Support for security policies. DBE-13138 [Redshift] Support for sort keys for tables and views. DBE-13024: [Redshift] Fixed the bug with the completely failed introspection. DBE-12442: [Oracle] Source code is retrieved correctly for Oracle 9i. DBE-13146: [Oracle] Type attributes are retrieved correctly for Oracle 11. DBE-12954: [BigQuery] No more missing schemas. DBE-12166: [BigQuery] Creating a temporary function doesn't re-run the introspection. Navigation and search DBE-12901: It's possible to navigate to the data source from the Database tab of the search. DBE-11738: The Context data source scope works again in search. DBE-12968: Color settings are respected again in search. DBE-13090: View DDL from data works again. DBE-5341: Go to related symbol has been added to the context menu of the object in the code editor. DBE-4960: [PostgreSQL] Go to related symbol works for navigating from triggers to the related procedure, and vice versa. DBE-13033: Navigation by foreign keys supports references to one column. DBE-13037: Scroll from editor works from the data editor. DBE-12967: [SQlite] Full-Text Search uses LIKE to search within LONGVARCHAR fields. Data viewer DBE-5435, DBE-11780, DBE-12827, DBE-11591: Fixed some bugs with missing ctid columns. DBE-13150: The data diff viewer works again. DBE-13126: Text search works when invoked a second time. DBE-12985: [MySQL]: The default value for the date field is now correct. DBE-12940: The Toggle Soft-Wrap and Toggle Formatting buttons don't disappear anymore. DBE-12594: Formatted mode for XML no longer removes all spaces. Code generation DBE-8654: Run routine generated code contains qualified objects. DBE-12934: Argument direction is taken into account when modifying the routine. DBE-12873 [BigQuery] Int64 is the default type when creating a new column. DBE-12892 [BigQuery] Dropping views now works. Code highlighting DBE-9813: [Clickhouse] Support for CREATE DICTIONARY. DBE-12996: [Clickhouse] Support for EXISTS (database | dictionary | view) statements. DBE-12993: [Clickhouse] Correct syntax highlighting for CREATE OR REPLACE. DBE-12994: [BigQuery] Support for EXPORT DATA, TABLESAMPLE SYSTEM, CREATE SCHEMA, ALTER SCHEMA, and DROP SCHEMA. That's all for today! The DataGrip team