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; }
