[ad_1]

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 KeywordsIN and BETWEEN
New OperatorsTernary (?), 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 FunctionsABS, 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 functionsIS_ARRAY, IS_BOOL, IS_NULL, IS_NUMBER, IS_OBJECT, IS_STRING, IS_DEFINED, and IS_PRIMITIVE
String functionsCONCAT, CONTAINS, ENDSWITH, INDEX_OF, LEFT, LENGTH, LOWER, LTRIM, REPLACE, REPLICATE, REVERSE, RIGHT, RTRIM, STARTSWITH, SUBSTRING, and UPPER
Array functionsARRAY_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.

[ad_2]

Source link