DocumentDB

BW-Tree

SQL queries

Cheat sheet

Built-in functions
Mathematical ABS, CEILING, EXP, FLOOR, LOG, LOG10, POWER, ROUND, SIGN, SQRT, SQUARE, TRUNC, ACOS, ASIN, ATAN, ATN2, COS, COT, DEGREES, PI, RADIANS, SIN, TAN
Type checking IS_ARRAY, IS_BOOL, IS_NULL, IS_NUMBER, IS_OBJECT, IS_STRING, IS_DEFINED, IS_PRIMITIVE
String CONCAT, CONTAINS, ENDSWITH, INDEX_OF, LEFT, LENGTH, LOWER, LTRIM, REPLACE, REPLICATE, REVERSE, RIGHT, RTRIM, STARTSWITH, SUBSTRING, UPPER
Array ARRAY_CONCAT, ARRAY_CONTAINS, ARRAY_LENGTH, ARRAY_SLICE
Geospatial ST_WITHIN, ST_DISTANCE, ST_INTERSECTS, ST_ISVALID, ST_ISVALIDDETAILED
Operators _    
arithmetic +, -, *, /, %    
bitwise |, &, ^, <,», »> (zero-fill right shift)    
logical AND, OR, NOT    
comparison =, !=, >, >=, <, <=, <>, ??    
string     (concatenate)
ternary ?    
Sample queries
Comparison (range) operators SELECT * FROM Families.children[0] c WHERE c.grade >= 5
Logical operators SELECT * FROM Families.children[0] c WHERE c.grade >= 5 AND c.isRegistered = true
Array Built-in functions SELECT Families.id FROM Families WHERE ARRAY_CONTAINS(Families.parents, {givenName: "Robin", familyName: "Wakefield" })
String Built-in functions SELECT Families.id, Families.address.city FROM Families WHERE STARTSWITH(Families.id, "Wakefield")
Parameterized SQL SELECT * FROM Families f WHERE f.lastName = @lastName AND f.address.state = @addressState
Intradocument JOINS SELECT f.id AS familyName, c.givenName AS childGivenName, c.firstName AS childFirstName, p.givenName AS petName FROM Families f JOIN c IN f.children JOIN p IN c.pets
Value keyword SELECT VALUE "Hello World", SELECT VALUE ABS(-4)
Object/Array Creation SELECT [f.address.city, f.address.state] AS CityState FROM Families f
Escape/quoted accessor SELECT f["lastName"] FROM Families f WHERE f["id"] = "AndersenFamily"
Ternary (?) and Coalesce (??) operators SELECT (c.grade < 5)? "elementary": ((c.grade < 9)? "junior": "high") AS gradeLevel FROM Families.children[0] c
IN keyword SELECT * FROM Families WHERE Families.address.state IN ("NY", "WA", "CA", "PA", "OH", "OR", "MI", "WI")
ORDER BY keyword SELECT f.id, f.address.city FROM Families f ORDER BY f.address.city
Geospatial functions SELECT * FROM Families f WHERE ST_Distance(f.location, {"type":"Point", "coordinates":[31.9, -4.8]}) < 30000
Type Built-in functions SELECT IS_DEFINED(f.lastName), IS_NUMBER(4) FROM Families f
BETWEEN keyword SELECT * FROM Families.children[0] c WHERE c.grade BETWEEN 1 AND 5
SQL - JavaScript UDF, define function (input, pattern) { return input.match(pattern) !== null; }
SQL - JavaScript UDF, use SELECT udf.REGEX_MATCH(Families.address.city, ".*eattle")

Execute Stored Procedure

« home