SQL for MongoDB

CLion 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.

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

SELECT  _id,
        `acquisitions`,
        `category_code`,
        `description`,
        email_address,
        phone_number
FROM companies WHERE category_code = 'social'
AND description = "Mobile Dating";

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. CLion 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, CLion 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:

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

will be translated to:

db.getSiblingDB("admin").getCollection("companies").aggregate([
  {
    $project: {"A": {$literal: new UnknownTypeConstructor()},
               "B": {$literal: new NumberInt()},
               "C": {$literal: new NumberInt(1)},
               "_id": 0}
  }
])

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:

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.

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.