New DocumentDB SQL Keywords, Operators and Functions
Posted on
2 min read
We’re pleased to announce some enhancements to the DocumentDB SQL grammar, including new keywords, operators and functions. The latest service update includes support for the IN and BETWEEN keywords, the ternary (?), coalescing (??), and bitwise shift operators, and a number of built-in functions.
Newly added keywords and operators:
| New Keywords | IN and BETWEEN |
| New Operators | Ternary (?), Coalesce (??), Bitwise shift operators (<<, >>, >>>) |
BETWEEN and IN work like in ANSI-SQL. BETWEEN can be used as a shorthand for range queries, and IN can be used to specify up to 100 values (by default) to be matched against an expression in the WHERE clause of a query. The coalesce, ternary and bitwise operators work like in higher-level programming languages like C# and JavaScript, and are useful for constructing JSON result-sets on the fly. To learn more, refer to this section in the SQL query tutorial.
Newly added Built-in functions:
| Mathematical Functions | ABS, CEILING, EXP, FLOOR, LOG, LOG10, POWER, ROUND, SIGN, SQRT, SQUARE, TRUNC, ACOS, ASIN, ATAN, ATN2, COS, COT, DEGREES, PI, RADIANS, SIN, and TAN |
| Type checking functions | IS_ARRAY, IS_BOOL, IS_NULL, IS_NUMBER, IS_OBJECT, IS_STRING, IS_DEFINED, and IS_PRIMITIVE |
| String functions | CONCAT, CONTAINS, ENDSWITH, INDEX_OF, LEFT, LENGTH, LOWER, LTRIM, REPLACE, REPLICATE, REVERSE, RIGHT, RTRIM, STARTSWITH, SUBSTRING, and UPPER |
| Array functions | ARRAY_CONCAT, ARRAY_CONTAINS, ARRAY_LENGTH, and ARRAY_SLICE |
With DocumentDB built-in functions, you'll be able to call common library routines like STARTSWITH or ROUND within queries instead of processing client-side or writing custom UDFs. This change also makes it easier to port applications written in other SQL and NoSQL platforms with similar functionality. Some of these functions like ABS and CEILING are similar to the T-SQL equivalents, but others like ARRAY_CONTAINS and IS_DEFINED are designed for working with JSON data with flexible schema. To learn more about built-in functions, please refer to this section in the SQL query tutorial article. Try these new features in the DocumentDB query playground or by creating your DocumentDB account. If you have any questions, please reach out to us on the Azure DocumentDB Developer Forums on MSDN or the developer forums on stack overflow. Please let us know if you would like us to support any additional functions or keywords through the Azure Feedback forum.