Skip to content

SQL Queries

Jump to bottom
Aryeh Citron edited this page Apr 13, 2026 · 24 revisions

The emulator includes a full SQL query engine built with Superpower parser combinators. It parses and executes Cosmos DB SQL queries against in-memory data, supporting 125+ built-in functions.

See also: Features for higher-level feature descriptions · API Reference for query method signatures · Known Limitations for behavioural differences from real Cosmos DB

Recent Improvements (v2.0.164)

  • DISTINCT + OFFSET/LIMIT ordering fixed: SELECT DISTINCT VALUE c.cat FROM c ORDER BY c.cat OFFSET 2 LIMIT 2 now correctly applies DISTINCT first, then OFFSET/LIMIT. Previously, OFFSET/LIMIT were applied before DISTINCT, producing wrong results when underlying data contained duplicates.
  • HAVING COUNT(c.field) null fix: HAVING COUNT(c.field) >= N in GROUP BY queries no longer counts documents where the field is explicitly null. Previously, SelectToken(path) != null returned true for JSON null values, leading to overcounting.
  • String function type safety: LOWER, UPPER, TRIM, LTRIM, RTRIM, CONCAT, STARTSWITH, ENDSWITH, CONTAINS, SUBSTRING, REPLACE, INDEX_OF, REPLICATE, STRING_EQUALS, REGEXMATCH, LEFT, RIGHT now return undefined for non-string arguments instead of coercing via .ToString(). This matches real Cosmos DB behaviour where these functions require string arguments.
  • 86 new query tests: Comprehensive V20 deep dive covering DISTINCT+OFFSET/LIMIT ordering with duplicates, HAVING COUNT null semantics, string function type safety for all 17 affected functions, REPLICATE boundary (10000 limit), complex feature interactions (JOIN+GROUP BY+HAVING+ORDER BY), ORDER BY type ranking across mixed types, and additional edge case coverage.

Recent Improvements (v2.0.98)

  • TOSTRING array/object → JSON string: TOSTRING([1,2,3]) now returns "[1,2,3]" and TOSTRING({"x":1}) returns "{\"x\":1}", matching Cosmos DB documentation. Previously returned undefined incorrectly.
  • TONUMBER(null) → undefined: TONUMBER(null) now returns undefined instead of null, matching Cosmos DB conversion semantics where null input produces undefined output.
  • 59 new query tests: Comprehensive V18 deep dive covering TOSTRING edge cases (integer→string, float→string, boolean→"true"/"false", array→JSON string, object→JSON string, string passthrough, null→undefined, missing→undefined), TONUMBER edge cases (integer string, float string, number passthrough, non-numeric string→undefined, boolean→undefined, null→undefined, array→undefined), TOBOOLEAN edge cases (true/false strings, bool passthrough, number→undefined, non-bool string→undefined, null→undefined), STRING_EQUALS (exact match, no match, case-sensitive default, case-insensitive with 3rd arg, null field→excluded), BETWEEN with strings (lexicographic comparison, null operand, missing field, NOT BETWEEN strings), COALESCE/null-coalesce (null→right, defined→left, undefined→right, chained 3-way), aggregate null/undefined handling (SUM ignores undefined, COUNT with field, AVG with undefined, MIN/MAX defined values), complex query combinations (TOP+DISTINCT+ORDER BY, GROUP BY function+HAVING, subquery with ORDER BY+LIMIT, multiple JOINs with cross-filtering, SELECT VALUE aggregate object+GROUP BY), IS_DEFINED edge cases (null-valued property→true, missing→false, nested path, NOT IS_DEFINED, combined conditions), and misc function edge cases (ARRAY_CONCAT empty/3+ arrays, INDEX_OF with start position, REPLACE empty search, SUBSTRING out-of-range, LEFT/RIGHT exceeding length, TRIM/LTRIM/RTRIM no-op).

Recent Improvements (v2.0.97)

  • Unary negate on null/undefined → undefined: -null, -c.missing, -'hello', and -true now return undefined instead of null, matching Cosmos DB three-value logic. Only numeric values produce defined results.
  • Bitwise NOT on null/undefined → undefined: ~null, ~c.missing, ~'hello', and ~true now return undefined instead of null, matching Cosmos DB three-value logic. Only integer values produce defined results.
  • LIKE with null → undefined: null LIKE '%', 'abc' LIKE null, and NOT LIKE with null operands now return undefined instead of false, matching Cosmos DB three-value logic for comparison operators. The EvaluateWhereExpressionIncludesUndefined method was extended to detect null operands in LIKE comparisons.
  • COALESCE all-undefined → undefined: COALESCE(c.missing1, c.missing2) when all arguments are undefined now returns undefined (omitted) instead of null. When all arguments are a mix of null and undefined, falls back to null. Only returns undefined when every argument is truly undefined.
  • 53 new query tests: Comprehensive V17 deep dive covering unary negate edge cases (null→undefined, undefined field→undefined, string→undefined, boolean→undefined, number→works), bitwise NOT edge cases (null→undefined, undefined→undefined, string→undefined, boolean→undefined, number→works), LIKE null edge cases (null value→undefined, null pattern→undefined, null field WHERE excludes row, NOT LIKE null excludes row, NOT of null LIKE→undefined), COALESCE function edge cases (all undefined→undefined, undefined then value→value, null then value→skips null, first defined→returns first), nested subqueries (SELECT VALUE with subquery, WHERE with subquery), empty container aggregates (COUNT→0, SUM→undefined, AVG→undefined, MIN→undefined, MAX→undefined), LIKE with non-string types (numeric value, boolean value, array→undefined), BETWEEN with null (null value→undefined, null bound→undefined), IN with null (null matches null in list, null not in list→false), ternary edge cases (null condition→false branch, zero→false branch, empty string→false branch, non-empty string→true branch), DISTINCT VALUE (mixed types deduplicate, null kept once), LIMIT/OFFSET edge cases (LIMIT 0→empty, OFFSET 0→no effect), LIKE field type coercion (number field→matches as string), multi-aggregate object literals, complex HAVING with AND, NOT BETWEEN, NOT LIKE with null.

Recent Improvements (v2.0.96)

  • LEFT/RIGHT negative count → undefined: LEFT('hello', -1) and RIGHT('hello', -1) now return undefined instead of null, matching Cosmos DB behaviour for invalid arguments.
  • TOBOOLEAN invalid input → undefined: TOBOOLEAN('hello'), TOBOOLEAN(42), and TOBOOLEAN(null) now return undefined instead of null, matching Cosmos DB conversion semantics. Only boolean values and parseable boolean strings ("true"/"false") produce defined results.
  • INTBITLEFTSHIFT/INTBITRIGHTSHIFT bounds check: Shift amounts < 0 or >= 64 now return undefined instead of producing undefined C# behaviour. Previously, C# would silently use only the low 6 bits of the shift amount, giving wrong results for shifts >= 64.
  • NOT NOT undefined three-value logic: WHERE NOT NOT (c.missing = 5) now correctly propagates undefined through nested NOT operations. Previously, double-NOT on undefined incorrectly returned true (included row) instead of undefined (excluded row). Fixed by adding NotCondition handling to EvaluateWhereExpressionIncludesUndefined.
  • 67 new query tests: Comprehensive V16 deep dive covering TOBOOLEAN edge cases (invalid string, number, null, already-bool, true/false strings), LEFT/RIGHT edge cases (negative count→undefined, zero→empty, exceeds length→full string), INTBITLEFTSHIFT/INTBITRIGHTSHIFT edge cases (shift by 64/128→undefined, negative shift→undefined, shift by 0→same value, shift by 63→valid), WHERE three-value logic (NOT undefined, double NOT, undefined OR true, undefined AND false, IS NULL vs undefined, NOT IS_DEFINED), mixed-type ORDER BY (null < bool < number < string rank, DESC reversal), GROUP BY (function expressions, undefined vs null separate groups, HAVING filter), aggregate empty sets (COUNT→0, SUM/AVG/MIN/MAX→undefined), DISTINCT+TOP combined, JOIN with empty/null/missing arrays, subquery edge cases (empty ARRAY, EXISTS true/false), COALESCE (undefined fallthrough, null fallthrough, both undefined), ternary/IIF (undefined/null/true conditions), LIKE (% matches all, _ matches single char, ESCAPE), BETWEEN (inclusive boundaries, undefined operand), IN (null in list, undefined field), string concat operator (||), object/array literals in SELECT, OFFSET/LIMIT (without ORDER BY, correct slice, beyond data).

Recent Improvements (v2.0.95)

  • INTDIV/INTMOD by zero → undefined: IntDiv(10, 0) and IntMod(10, 0) now return undefined instead of null, matching Cosmos DB three-value logic.
  • ARRAY_LENGTH non-array → undefined: ARRAY_LENGTH('not_array') and ARRAY_LENGTH(42) now return undefined instead of null, matching Cosmos DB behaviour.
  • 109 new query tests: Comprehensive V15 deep dive covering math functions (ABS, CEILING, FLOOR, ROUND with precision, SQRT including negative, SQUARE, LOG natural and with base, LOG10, EXP, SIN/COS/TAN, ASIN/ACOS/ATAN, ATN2, DEGREES, RADIANS, PI, SIGN, TRUNC, RAND, NumberBin, POWER), string functions (LTRIM, RTRIM, TRIM, REVERSE, LEFT, RIGHT, INDEX_OF, REPLICATE, ToString, ToNumber, ToBoolean, null propagation for UPPER/LOWER/CONCAT/LENGTH/STARTSWITH), integer/bitwise functions (IntAdd, IntSub, IntMul, IntDiv, IntMod, IntBitOr, IntBitXor, IntBitNot, IntBitLeftShift, IntBitRightShift, IntDiv/IntMod by zero), date/time functions (GetCurrentDateTime, GetCurrentTimestamp, DateTimeAdd hours/days, DateTimePart year/month/day/hour, DateTimeDiff days, DateTimeFromParts, DateTimeBin, DateTimeToTicks, TicksToDateTime roundtrip, DateTimeToTimestamp, TimestampToDateTime roundtrip, GetCurrentTicks), type checking (IS_FINITE_NUMBER, IS_INTEGER true/false, TYPE for all types), array functions (ARRAY_LENGTH including empty/non-array, ARRAY_CONCAT, ARRAY_CONTAINS_ANY, ARRAY_CONTAINS_ALL, SetIntersect, SetUnion, SetDifference), conditional functions (IIF true/false/null), geospatial functions (ST_DISTANCE, ST_WITHIN true/false, ST_ISVALID valid/invalid), full-text search (FullTextContains match/no-match, FullTextContainsAll, FullTextContainsAny, FullTextScore), arithmetic edge cases (division by zero, modulo by zero), ORDER BY/GROUP BY edge cases, JOIN with nested paths and GROUP BY, subquery with aggregate.

Recent Improvements (v2.0.94)

  • GROUP BY nested aggregates in object/array literals: SELECT VALUE {"cnt": COUNT(1)} FROM c GROUP BY c.pk now correctly detects aggregates nested inside object and array literals, instead of returning raw group values. A new recursive EvaluateGroupByProjectionExpression method walks the projection AST to evaluate aggregates in any position.
  • BitwiseOp non-integer → undefined: IntBitAnd(1.5, 3), IntBitOr(null, 1), and similar bitwise operations on non-integer or null/undefined inputs now return undefined instead of null, matching Cosmos DB three-value logic.
  • MathOp null → undefined: POWER(null, 2), LOG(null), and other math operations with null or undefined inputs now return undefined instead of null.
  • STRING_EQUALS null args → undefined: STRING_EQUALS(null, 'a') and STRINGEQUALS('a', null) now return undefined instead of null, matching Cosmos DB.
  • String concat (||) null → undefined: null || 'a' and 'a' || null now return undefined instead of null, matching Cosmos DB three-value semantics.
  • 68 new query edge case tests: Comprehensive V14 deep dive covering GROUP BY advanced scenarios (object/array literals with aggregates, HAVING with COUNT(field), boolean GROUP BY keys, compound ORDER BY after GROUP BY, GROUP BY with coalesce/IIF, single group with aggregate expression, COUNT DISTINCT simulation), undefined/null propagation (MathOp null→undefined, StringEquals null, bitwise non-integer, IIF undefined condition, coalesce all undefined, nested undefined property, null arithmetic, ternary undefined branches, NOT undefined, IN with undefined), bitwise integer edge cases (IntBitAnd/Or/Xor with zero, IntMul large numbers, IntDiv/Mod negative, IntAdd overflow, IntSub underflow, IntMod by zero), string null edge cases (UPPER/LOWER/LENGTH null→undefined, REVERSE empty, INDEX_OF both empty, LEFT/RIGHT zero, SUBSTRING zero length, REPLACE null), subquery advanced (ARRAY with WHERE/TOP/DISTINCT, EXISTS with JOIN, NOT EXISTS), complex ORDER BY (nested functions, arithmetic expressions, coalesce, three fields mixed directions, type rank, ternary, LENGTH, GROUP BY+ORDER BY COUNT), complex projections (object/array literals in SELECT, nested functions, arithmetic, IIF, all 5 aggregates), and cross-platform reliability (ordinal string sort, floating point precision, UTC datetime, ToString culture, regex dot notation, invariant number parsing).

Recent Improvements (v2.0.93)

  • REGEXMATCH invalid regex → undefined: REGEXMATCH('test', '[invalid') with a malformed regex pattern now returns undefined instead of throwing ArgumentException, matching Cosmos DB behaviour.
  • ARRAY_CONCAT null → undefined: ARRAY_CONCAT([1], null) now returns undefined (instead of an empty array), matching Cosmos DB three-value logic where any null array argument makes the entire result undefined.
  • IS_OBJECT/IS_ARRAY with parameters: IS_OBJECT(@obj) and IS_ARRAY(@arr) with parameter values now correctly return true when the parameter is a JObject/JArray, instead of always returning false.
  • BinaryOpToString bitwise operators: The SQL expression serializer now correctly outputs &, |, ^ for bitwise AND, OR, XOR operations instead of the enum name string.
  • 64 new query edge case tests: Comprehensive V13 deep dive covering string function edge cases (STARTSWITH/ENDSWITH/CONTAINS empty string, REPLACE empty find, INDEX_OF empty search, REGEXMATCH invalid pattern, TRIM whitespace-only, STRINGSPLIT consecutive delimiters, CONCAT 5+ args), math function boundaries (POWER(0,0), POWER(-1,0.5), LOG base 1, LOG(1), TRUNC negative, SIGN(-0), ASIN/ACOS exact boundaries), array edge cases (ARRAY_SLICE negative length, ARRAY_CONCAT null, Set operations with duplicates, ObjectToArray/ArrayToObject empty, CHOOSE fractional index), date/time edge cases (DATETIMEADD negative, DATETIMEDIFF negative/zero, DATETIMEFROMPARTS invalid, DATETIMEPART ms/ns), operator edge cases (BETWEEN strings, NOT BETWEEN, IN with null/mixed types, nested ternary, non-bool ternary, LIKE multiple wildcards/escaped underscore, null coalesce chain, string concat operator), cross-feature interactions (DISTINCT+TOP+ORDER BY, OFFSET beyond count, LIMIT 0, GROUP BY+ORDER BY+TOP, JOIN+DISTINCT, multiple aggregates, EXISTS complex WHERE, BETWEEN with parameters, JOIN+GROUP BY+aggregate), parameter edge cases (reuse, empty JArray/JObject, null IS_NULL, duplicate last-wins, BETWEEN params), and projection edge cases (SELECT VALUE null/undefined, 4-level nested path, system properties, same field multiple aliases, SELECT VALUE COUNT).

Recent Improvements (v2.0.92)

  • DATETIMEDIFF null args → undefined: DATETIMEDIFF('dd', null, '...') now returns undefined instead of null, matching Cosmos DB.
  • TICKSTODATETIME null → undefined: TICKSTODATETIME(null) now returns undefined instead of null.
  • DATETIMETOTIMESTAMP null → undefined: DATETIMETOTIMESTAMP(null) now returns undefined instead of null.
  • TIMESTAMPTODATETIME null → undefined: TIMESTAMPTODATETIME(null) now returns undefined instead of null.
  • DATETIMEBIN negative/zero binSize → undefined: DATETIMEBIN('...', 'dd', 0) and negative binSize now return undefined instead of null.
  • RAND() thread safety: RAND() now uses Random.Shared instead of creating a new Random() per call, avoiding race conditions.
  • ChangeFeed processor flaky test fix: ChangeFeed_Processor_DeliversOnlyLatestVersion race condition resolved.
  • 75 new query edge case tests: Deep dive V12 covering date/time null handling, NumberBin edge cases, LIKE ESCAPE patterns, string function null inputs, math NaN/Infinity boundaries, aggregate edge cases, operator edge cases (ternary null/undefined, null coalesce, IN with null, BETWEEN with null, division/modulo by zero), SELECT/JOIN/GROUP BY edge cases, type coercion, subquery EXISTS/ARRAY, and error handling.

Recent Improvements (v2.0.91)

  • Integer arithmetic overflow fix: 9223372036854775807 + 1 (long.MaxValue + 1) and similar overflows now correctly return a double instead of silently wrapping to a negative number. The ArithmeticOp helper now checks that the result fits within long range before casting back from double.
  • DATETIMEADD overflow fix: DATETIMEADD('yyyy', 100000, '2000-01-01T00:00:00Z') now returns undefined instead of throwing an unhandled ArgumentOutOfRangeException.
  • CHOOSE out-of-bounds fix: CHOOSE(0, 'a', 'b') and CHOOSE(5, 'a', 'b') now return undefined (field omitted from projection) instead of null, matching real Cosmos DB behaviour.
  • GetCurrentDateTime test fix: Flaky midnight-boundary test fixed by parsing with DateTimeStyles.RoundtripKind to preserve UTC kind, preventing timezone-induced date mismatch on non-UTC systems.
  • 60 new query edge case tests: Comprehensive coverage added for integer overflow/underflow, bit shift edge cases (≥64 bits, negative), INTDIV/INTMOD by zero, NUMBERBIN zero/negative bin size, REPLICATE boundary (10000/10001), INDEX_OF/CONTAINS/STARTSWITH/ENDSWITH with empty strings, POWER(0,0)/POWER(0,-1), LOG(0)/LOG(1), SQRT(0), TRUNC large doubles, ARRAY_SLICE negative start beyond length, STRINGTOARRAY/STRINGTOOBJECT invalid JSON, ORDER BY mixed null/undefined/number/string type ranking, DATETIMEADD/DATETIMEPART overflow, nested COALESCE, aggregate subqueries, BETWEEN equal bounds, IN single/mixed types, NOT IN, LIKE underscore/percent wildcards, TYPE function, IS_NULL/IS_DEFINED null vs undefined, CHOOSE valid/zero index, SET operations, and more.

Recent Improvements (v2.0.90)

  • CONCAT null → undefined: CONCAT('a', null, 'b') now returns undefined instead of "ab", matching Cosmos DB three-value logic where any null argument makes the entire result undefined.
  • REPLICATE edge cases → undefined: REPLICATE('x', -1) and REPLICATE('a', 10001) now return undefined instead of null/capped values. Cosmos DB treats negative counts and counts exceeding 10,000 as undefined.
  • ROUND negative precision: ROUND(1234.5, -2) now correctly rounds to the nearest power of ten (→ 1200). Previously threw ArgumentOutOfRangeException as Math.Round doesn't support negative precision natively.
  • NumberBin zero/negative binSize → undefined: NumberBin(42, 0) and NumberBin(42, -5) now return undefined instead of null, matching Cosmos DB.
  • Arithmetic on null → undefined: null + 1, null * 2, null - 3, null % 2 now return undefined (instead of null), matching Cosmos DB three-value arithmetic semantics.
  • NOT null → undefined: NOT null now returns undefined (instead of true), matching Cosmos DB three-value logic.
  • StringSplit empty delimiter → undefined: StringSplit('abc', '') now returns undefined instead of throwing.
  • Object parameter equality: WHERE c.nested = @obj with anonymous C# object parameters now correctly deep-compares with JObject document fields using JToken.DeepEquals.
  • 39 new query tests: Comprehensive coverage added for CONCAT non-string/null args, REPLICATE edge cases, ROUND negative precision, NumberBin zero/negative bin, arithmetic on null, mixed-type comparisons (number vs string, bool vs string), DISTINCT multiple fields, BETWEEN with date strings, IIF non-boolean conditions, LIKE with OR patterns, NOT on null, coalesce three args, JOIN on non-array/null/undefined fields, IS_DEFINED deeply nested, cross-partition pagination with continuation tokens, DateTimeDiff same/reversed args, DateTimeBin truncation, GetCurrentDateTime consistency, array/object parameter handling, SET_INTERSECT/UNION/DIFFERENCE, ARRAY_SLICE zero length, StringSplit edge cases.

Recent Improvements (v2.0.89)

  • New function: IS_NAN(expr): Returns true when the value is NaN (Not-a-Number). Returns false for numbers, strings, booleans, null, and undefined/missing fields.
  • New function: TYPE(expr): Returns the JSON type name of an expression as a string: "string", "number", "boolean", "null", "array", or "object". Returns undefined for missing fields (row excluded from SELECT VALUE).
  • String functions null → undefined: UPPER, LOWER, TRIM, LTRIM, RTRIM, REVERSE, LENGTH, LEFT, RIGHT, REPLACE now return undefined (instead of null) when given explicit null input, matching Cosmos DB three-value logic semantics.
  • STARTSWITH/ENDSWITH/CONTAINS null → undefined: These functions now return undefined when the input string is null (instead of false), matching Cosmos DB. In WHERE clauses, the effect is the same (row excluded).
  • LOG(val, base) NaN/Infinity guard: LOG(10, 1) (infinity result) and similar edge cases now return undefined instead of raw Infinity.
  • REPLACE empty find string: REPLACE('hello', '', 'x') no longer throws ArgumentException — empty find string returns the original string unchanged.
  • DateTimeToTicks invalid format → undefined: DateTimeToTicks('not-a-date') now returns undefined instead of null.
  • 68 new query tests: Comprehensive coverage added for IS_NAN, TYPE(), string function null input semantics, math edge cases (COT(0), LOG base 1, ATN2(0,0)), array set operations, aggregate null handling, date/time edge cases, conditional expressions, clause interactions (BETWEEN reversed bounds, IN mixed types, NOT BETWEEN/IN), and cross-feature interactions (GROUP BY + ORDER BY, JOIN + GROUP BY, DISTINCT + ORDER BY).

Recent Improvements (v2.0.86)

  • SUBSTRING negative index safety: SUBSTRING(str, -1, 3) and SUBSTRING(str, 0, -1) no longer throw ArgumentOutOfRangeException — negative start and length values are clamped to zero.
  • LEFT/RIGHT negative count safety: LEFT(str, -1) and RIGHT(str, -1) no longer throw — negative counts return null.
  • 77 new query edge case tests: Comprehensive coverage added for null inputs to functions, undefined (missing field) inputs, empty strings, type mismatches, negative indices, WHERE boundary conditions (WHERE true, WHERE false, WHERE null), IN/BETWEEN/LIKE special characters, nested function calls, multiple aggregates in SELECT, and type-check function edge cases.

Recent Improvements (v2.0.85)

  • NaN/Infinity → undefined: Math functions (SQRT(-1), LOG(0), POWER(0,-1)) and arithmetic (1/0, 0%0) now return undefined instead of NaN/Infinity, matching Cosmos DB semantics.
  • DateTime null → undefined: DATETIMEADD, DATETIMEPART, DATETIMEFROMPARTS, DATETIMEBIN now return undefined for null/invalid inputs instead of null.
  • SUBSTRING null → undefined: SUBSTRING(null, ...) now returns undefined.
  • GROUP BY null vs undefined: GROUP BY now correctly distinguishes null from undefined (missing field), producing separate groups.
  • Multi-JOIN source alias: JOIN t IN g.tags (where g is a previous JOIN alias) now resolves correctly.
  • ARRAY_CONTAINS with parameter array: ARRAY_CONTAINS(@names, c.field) where @names is an array parameter now works in WHERE clauses.
  • Static DateTime race: GetCurrentDateTime(), GetCurrentTimestamp(), and GetCurrentTicks() now capture DateTime.UtcNow once per query to avoid inconsistencies.

Recent Improvements (v2.0.80)

  • COUNT(c.field) null semantics: COUNT(c.field) now correctly excludes documents where the field is null, matching Cosmos DB behaviour. COUNT(1) and COUNT(*) still count all documents.
  • Subquery TOP/ORDER BY ordering: Subqueries with both TOP and ORDER BY now apply sorting before taking the top N results.
  • Cross-type equality: Strict type comparison — true = "True" and 42 = "42" now correctly return false, matching Cosmos DB semantics.
  • LIKE with undefined fields: LIKE on an undefined (missing) field now returns false instead of crashing.
  • String concat (||) with undefined: undefined || "text" now returns undefined instead of "text".

Clauses

Clause Examples
SELECT SELECT *, SELECT c.name, c.age, SELECT VALUE c.name
SELECT DISTINCT SELECT DISTINCT c.category
SELECT TOP SELECT TOP 10 * FROM c, SELECT DISTINCT TOP 5 VALUE c.cat FROM c
FROM FROM c, FROM s IN c.scores (top-level array iteration)
WHERE c.age > 30 AND c.active = true
ORDER BY ORDER BY c.name ASC, c.date DESC
ORDER BY RANK ORDER BY RANK FullTextScore(c.text, ['term']) (for full-text search)
GROUP BY / HAVING GROUP BY c.category HAVING COUNT(1) > 5
OFFSET / LIMIT OFFSET 10 LIMIT 20
JOIN JOIN t IN c.tags (array expansion, multiple JOINs)

Operators

Category Supported
Comparison =, !=, <>, <, >, <=, >=
Logical AND, OR, NOT
Arithmetic +, -, *, /, %
String concat ||
Null coalesce ??
Ternary condition ? ifTrue : ifFalse
Bitwise &, |, ^, ~
Range BETWEEN low AND high, NOT BETWEEN low AND high
Membership IN ('a', 'b', 'c'), NOT IN (...)
Pattern LIKE '%pattern%', NOT LIKE, LIKE ... ESCAPE (with % and _ wildcards)
Null checks IS NULL, IS NOT NULL

Built-in Functions

String Functions

Function Example
UPPER(str) SELECT VALUE UPPER(c.name) FROM c
LOWER(str) SELECT VALUE LOWER(c.name) FROM c
LTRIM(str) SELECT VALUE LTRIM(c.name) FROM c
RTRIM(str) SELECT VALUE RTRIM(c.name) FROM c
TRIM(str) SELECT VALUE TRIM(c.name) FROM c
SUBSTRING(str, start, length) SELECT VALUE SUBSTRING(c.name, 0, 3) FROM c
LENGTH(str) SELECT VALUE LENGTH(c.name) FROM c
CONCAT(str1, str2, ...) SELECT VALUE CONCAT(c.first, ' ', c.last) FROM c
CONTAINS(str, substr [, ignoreCase]) SELECT * FROM c WHERE CONTAINS(c.name, 'ali', true)
STARTSWITH(str, prefix [, ignoreCase]) SELECT * FROM c WHERE STARTSWITH(c.name, 'A', true)
ENDSWITH(str, suffix [, ignoreCase]) SELECT * FROM c WHERE ENDSWITH(c.name, 'e', true)
INDEX_OF(str, substr) SELECT VALUE INDEX_OF(c.name, 'l') FROM c
REPLACE(str, old, new) SELECT VALUE REPLACE(c.name, 'old', 'new') FROM c
REVERSE(str) SELECT VALUE REVERSE(c.name) FROM c
LEFT(str, count) SELECT VALUE LEFT(c.name, 3) FROM c
RIGHT(str, count) SELECT VALUE RIGHT(c.name, 3) FROM c
REPLICATE(str, count) SELECT VALUE REPLICATE('x', 5) FROM c
REGEXMATCH(str, pattern [, modifiers]) SELECT * FROM c WHERE REGEXMATCH(c.email, '^[a-z]+@', 'i')
StringEquals(str1, str2 [, ignoreCase]) SELECT * FROM c WHERE StringEquals(c.name, 'JOHN', true)
StringJoin(separator, array) SELECT VALUE StringJoin(',', c.tags) FROM c
StringSplit(str, delimiter) SELECT VALUE StringSplit(c.csv, ',') FROM c

Optional arguments: CONTAINS, STARTSWITH, and ENDSWITH accept an optional boolean 3rd argument for case-insensitive matching. REGEXMATCH accepts an optional 3rd argument with modifier flags: 'i' (ignore case), 'm' (multiline), 's' (single-line), 'x' (ignore whitespace).

Type-Checking Functions

Function Returns true when...
IS_ARRAY(expr) Value is an array
IS_BOOL(expr) Value is a boolean
IS_NULL(expr) Value is null
IS_DEFINED(expr) Property exists on the document
IS_NUMBER(expr) Value is a number
IS_OBJECT(expr) Value is a JSON object
IS_STRING(expr) Value is a string
IS_PRIMITIVE(expr) Value is string, number, boolean, or null
IS_FINITE_NUMBER(expr) Value is a finite number (not NaN/Infinity)
IS_INTEGER(expr) Value is an integer
IS_NAN(expr) Value is NaN (Not-a-Number)
TYPE(expr) Returns type name: "string", "number", "boolean", "null", "array", "object"

Math Functions

Function Description
ABS(num) Absolute value
CEILING(num) Ceiling (round up)
FLOOR(num) Floor (round down)
ROUND(num [, precision]) Round to nearest integer, or to N decimal places
SQRT(num) Square root
SQUARE(num) Square (num²)
POWER(base, exp) Exponentiation
EXP(num) e^num
LOG(num [, base]) Natural logarithm, or logarithm in given base
LOG10(num) Base-10 logarithm
SIGN(num) Sign (-1, 0, or 1)
TRUNC(num) Truncate to integer
PI() π constant
SIN(num), COS(num), TAN(num) Trigonometric functions
COT(num) Cotangent (1/tan)
ASIN(num), ACOS(num), ATAN(num) Inverse trigonometric functions
ATN2(y, x) Two-argument arctangent
DEGREES(radians) Radians to degrees
RADIANS(degrees) Degrees to radians
RAND() Random number [0, 1)
NumberBin(num, binSize) Bin a number to the nearest multiple

Integer Math Functions

Function Description
IntAdd(a, b) Integer addition
IntSub(a, b) Integer subtraction
IntMul(a, b) Integer multiplication
IntDiv(a, b) Integer division
IntMod(a, b) Integer modulo
IntBitAnd(a, b) Bitwise AND
IntBitOr(a, b) Bitwise OR
IntBitXor(a, b) Bitwise XOR
IntBitNot(a) Bitwise NOT
IntBitLeftShift(a, b) Left shift
IntBitRightShift(a, b) Right shift

Array Functions

Function Example
ARRAY_CONTAINS(arr, val) SELECT * FROM c WHERE ARRAY_CONTAINS(c.tags, 'urgent')
ARRAY_CONTAINS_ANY(arr, searchArr) true if any element of searchArr exists in arr
ARRAY_CONTAINS_ALL(arr, searchArr) true if all elements of searchArr exist in arr
ARRAY_LENGTH(arr) SELECT VALUE ARRAY_LENGTH(c.items) FROM c
ARRAY_SLICE(arr, start, length) SELECT VALUE ARRAY_SLICE(c.items, 0, 3) FROM c
ARRAY_CONCAT(arr1, arr2) SELECT VALUE ARRAY_CONCAT(c.tags, c.labels) FROM c
SetIntersect(arr1, arr2) Set intersection
SetUnion(arr1, arr2) Set union
SetDifference(arr1, arr2) Set difference (elements in arr1 not in arr2)
CHOOSE(index, val1, val2, ...) 1-based index selection from argument list
ObjectToArray(object) Convert {a:1, b:2} to [{k:"a", v:1}, ...]
ArrayToObject(array) Convert [{k:"a", v:1}, ...] to {a:1, b:2} (inverse of ObjectToArray)

Conversion Functions

Function Description
TOSTRING(value) Convert to string
TONUMBER(value) Convert to number
TOBOOLEAN(value) Convert to boolean
StringToArray(str) Parse JSON array string
StringToBoolean(str) Parse boolean string
StringToNull(str) Parse "null" string
StringToNumber(str) Parse number string
StringToObject(str) Parse JSON object string

Date/Time Functions

Function Description
GetCurrentDateTime() Current UTC datetime as ISO string
GetCurrentTimestamp() Current UTC as Unix timestamp (ms)
GetCurrentTicks() Current UTC as .NET ticks
GetCurrentDateTimeStatic() Same datetime for all items in a query
GetCurrentTimestampStatic() Same timestamp for all items in a query
GetCurrentTicksStatic() Same ticks for all items in a query
DateTimeAdd(part, num, datetime) Add to a datetime
DateTimePart(part, datetime) Extract part of a datetime
DateTimeDiff(part, start, end) Difference between two datetimes
DateTimeBin(datetime, part, binSize, [origin]) Bin a datetime to intervals
DateTimeFromParts(year, month, day, hour, min, sec, ms) Construct a datetime
DateTimeToTicks(datetime) Convert ISO datetime to .NET ticks
DateTimeToTimestamp(datetime) Convert ISO datetime to Unix timestamp (ms)
TicksToDateTime(ticks) Convert .NET ticks to ISO datetime
TimestampToDateTime(timestamp) Convert Unix timestamp (ms) to ISO datetime

Aggregate Functions

Function Description
COUNT(expr), COUNT(1), or COUNT(*) Count items
SUM(expr) Sum numeric values
AVG(expr) Average of numeric values
MIN(expr) Minimum value
MAX(expr) Maximum value

Conditional Functions

Function Description
IIF(condition, ifTrue, ifFalse) Inline conditional (only boolean true triggers true branch; numbers, strings, etc. yield false branch)
COALESCE(expr1, expr2, ...) First non-null/undefined value

Item Functions

Function Description
DOCUMENTID(item) Returns the document's resource ID

Geospatial Functions

Function Implementation
ST_DISTANCE(point1, point2) Haversine formula (metres)
ST_WITHIN(point, region) Point-in-polygon (ray casting) + point-in-circle (haversine radius)
ST_INTERSECTS(geo1, geo2) Point-point, point-polygon, polygon-polygon overlap
ST_ISVALID(geojson) Full GeoJSON validation (Point, Polygon, LineString, MultiPoint)
ST_ISVALIDDETAILED(geojson) Returns { valid, reason } with specific error messages
ST_AREA(polygon) Spherical excess formula

All geospatial functions use real geometric calculations. Results may differ slightly from Cosmos DB's exact values. See Known Limitations for details on precision differences.

Vector Functions

Function Implementation
VECTORDISTANCE(vec1, vec2) Cosine similarity (default). Returns -1 to +1
VECTORDISTANCE(vec1, vec2, bruteForce) 3rd bool arg accepted but ignored (always brute-force)
VECTORDISTANCE(vec1, vec2, false, {distanceFunction:'cosine'}) Cosine similarity: `dot(a,b) / (
VECTORDISTANCE(vec1, vec2, false, {distanceFunction:'dotproduct'}) Dot product: Σ(a[i] × b[i])
VECTORDISTANCE(vec1, vec2, false, {distanceFunction:'euclidean'}) Euclidean distance: √Σ(a[i] - b[i])²

Works in SELECT projections, WHERE filters, and ORDER BY clauses. Supports vectors of any dimensionality (tested up to 2000). Returns null for mismatched dimensions, missing vector properties, zero-magnitude vectors (cosine), non-numeric elements, multi-dimensional arrays, or Infinity/NaN overflow results. Additional options (dataType, searchListSizeMultiplier, filterPriority) are accepted but ignored. Unknown distance functions silently fall back to cosine. Extra arguments beyond the 4th are silently ignored. Works with arithmetic expressions (VectorDistance(...) * 100), function composition (ABS(VectorDistance(...))), and conditional logic (IIF(VectorDistance(...) > 0.5, ...)).

-- Find the 10 most similar documents by cosine similarity
SELECT TOP 10 c.title, VectorDistance(c.embedding, [0.1, 0.2, 0.3]) AS score
FROM c
ORDER BY VectorDistance(c.embedding, [0.1, 0.2, 0.3]) DESC

-- Filter by similarity threshold
SELECT c.id FROM c
WHERE VectorDistance(c.embedding, [0.1, 0.2, 0.3]) > 0.5

-- Arithmetic on vector scores
SELECT c.id, VectorDistance(c.embedding, [0.1, 0.2, 0.3]) * 100 AS pctScore
FROM c

No vector index policy or container configuration is required. The emulator always performs exact (brute-force) distance computation, which is correct for testing but does not simulate ANN index behaviour (DiskANN, quantizedFlat, flat). See Known Limitations for details.

Full-Text Search Functions

The emulator provides approximate implementations of all four Cosmos DB full-text search functions using case-insensitive substring matching. Real Cosmos DB uses NLP tokenisation and BM25 scoring.

Function Description
FullTextContains(field, term) true if the field contains the search term
FullTextContainsAll(field, term1, term2, ...) true if the field contains all search terms
FullTextContainsAny(field, term1, term2, ...) true if the field contains any search term
FullTextScore(field, [term1, term2, ...]) Returns a relevance score (naive term-frequency count)

Use ORDER BY RANK FullTextScore(...) to sort results by relevance:

-- Find documents containing 'database', sorted by relevance
SELECT * FROM c
WHERE FullTextContains(c.description, 'database')
ORDER BY RANK FullTextScore(c.description, ['database', 'cosmos'])

Note: Real Cosmos DB requires a full-text indexing policy. The emulator skips this validation — queries work on any container without configuration. Matching is approximate (case-insensitive substring, no stemming, no BM25 scoring). See Known Limitations for details.

Parameterised Queries

var query = new QueryDefinition(
        "SELECT * FROM c WHERE c.status = @status AND c.age > @minAge")
    .WithParameter("@status", "active")
    .WithParameter("@minAge", 21);

var iterator = container.GetItemQueryIterator<MyDoc>(query);

Subqueries

-- EXISTS
SELECT * FROM c WHERE EXISTS(
    SELECT VALUE 1 FROM t IN c.tags WHERE t = 'important')

-- ARRAY()
SELECT c.id, ARRAY(
    SELECT VALUE t FROM t IN c.tags WHERE t != 'draft') AS filteredTags
FROM c

-- Scalar subqueries in SELECT and WHERE
SELECT (SELECT VALUE COUNT(1) FROM t IN c.items) AS itemCount FROM c

ORDER BY, OFFSET, and LIMIT in Subqueries

Subqueries support ORDER BY, OFFSET, and LIMIT clauses, evaluated within the subquery scope:

-- Sorted subquery
SELECT ARRAY(SELECT VALUE s FROM s IN c.scores ORDER BY s DESC) AS sorted FROM c

-- Paginated subquery (skip 1, take 2)
SELECT ARRAY(SELECT VALUE s FROM s IN c.scores OFFSET 1 LIMIT 2) AS page FROM c

-- Combined: sort, then paginate
SELECT ARRAY(SELECT VALUE s FROM s IN c.scores ORDER BY s ASC OFFSET 1 LIMIT 3) AS page FROM c

User-Defined Functions (UDFs)

Register C# functions callable as udf.name() in SQL. See API Reference for the full RegisterUdf signature.

container.RegisterUdf("IsEven", args =>
{
    if (args[0] is not long num) return false;
    return num % 2 == 0;
});

var iterator = container.GetItemQueryIterator<dynamic>(
    "SELECT * FROM c WHERE udf.IsEven(c.value)");

Parser API

The SQL parser is also available directly for advanced scenarios. See API Reference for the full class reference.

// Parse a query
var parsed = CosmosSqlParser.Parse("SELECT c.name FROM c WHERE c.age > 30");

// Inspect the AST
Console.WriteLine(parsed.FromAlias);      // "c"
Console.WriteLine(parsed.Fields[0].Alias); // "name"

// Try-parse (no exception on failure)
if (CosmosSqlParser.TryParse(sql, out var result))
{
    // Use result
}

Home

Getting Started

Integration & Dependency Injection

Data Management

Reference

Help

Clone this wiki locally