SQL for MongoDB

Data types

AppCode 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";

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. AppCode 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, AppCode 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}
  }
])

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:

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.

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

GROUP BY

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

HAVING

AppCode supports the HAVING clause.

ORDER BY

AppCode supports the ORDER BY clause. Instead of ORDER BY, you can use SORT BY.

LIMIT

AppCode supports the LIMIT clause.

OFFSET

AppCode supports OFFSET clause.