33 new SDBQL functions, and FOR x IN anything
SoliDB 1.2.2 and 1.3.0 add 33 functions to SDBQL. Most of them replace something that used to take a subquery per row, a trip back to application code, or a helper every project ends up writing: the empty days in a report, a join, an audit diff, accent-insensitive search, an IBAN check. And one parser fix: FOR x IN now takes any expression.
Every result on this page was produced by running the query on a 1.3.0 server.
Reports that show the empty days
A daily sales chart built from COLLECT has a gap wherever nothing happened: no orders on Tuesday, no row for Tuesday. DATE_SERIES lists every date in a range, so the report can iterate over the calendar instead of over the data, and look each day up in counts computed once:
LET per_day = COUNT_BY((FOR o IN orders RETURN o), o -> DATE_TRUNC(o.at, "day")) FOR d IN DATE_SERIES("2024-03-01", "2024-03-04", "day") RETURN {day: LEFT(d, 10), orders: per_day[d] || 0}
[
{"day": "2024-03-01", "orders": 2},
{"day": "2024-03-02", "orders": 0},
{"day": "2024-03-03", "orders": 1},
{"day": "2024-03-04", "orders": 0}
]The keys line up because DATE_SERIES returns dates in the same shape as DATE_TRUNC. Each date is computed from the start — start plus k steps — so a monthly series from January 31 lands on the last day of February and comes back to the 31st in March, instead of drifting to the 28th.
DATE_SERIES(start, end, unit, step?, tz?) 1.3.0
Every date from start to end inclusive, step units apart. Each date is start + k·step, so a monthly series from the 31st lands on the last day of shorter months and comes back to the 31st. Accepts quarter; capped at 100,000 dates.
RETURN DATE_SERIES("2024-01-31", "2024-03-31", "month")
[ "2024-01-31T00:00:00.000Z", "2024-02-29T00:00:00.000Z", "2024-03-31T00:00:00.000Z" ]
DATE_END_OF(date, unit, tz?) 1.3.0
The last millisecond of the period holding a date — the other end of DATE_TRUNC, so a month filter no longer needs truncate, add one month, subtract a millisecond. Quarters work; with a timezone, the period is the local one.
RETURN [DATE_END_OF("2024-02-10", "month"), DATE_END_OF("2024-05-10", "quarter")]
["2024-02-29T23:59:59.999Z", "2024-06-30T23:59:59.999Z"]
DATE_PARSE(text, format | [formats], tz?) 1.2.2
The inverse of DATE_FORMAT, added in 1.2.2 for imports: read a date in a known format and get ISO-8601 UTC back. Pass an array of formats to try them in order.
RETURN DATE_PARSE("24/09/2026 14:05", "%d/%m/%Y %H:%M", "Europe/Paris")
"2026-09-24T12:05:00.000Z"
Joins in one read, and other array work
The usual way to attach a customer to each order is a subquery per order. KEY_BY reads the customers once into an object keyed by _key, and each order then looks its customer up in memory:
LET users = KEY_BY((FOR u IN users RETURN u), "_key") FOR o IN orders2 RETURN {total: o.total, customer: users[o.user_id].name}
[{"customer": "Ann", "total": 15}, {"customer": "Bo", "total": 40}]KEY_BY(arr, x -> key | "path") 1.3.0
Array to lookup object. The key is an attribute path or a lambda; keys become strings, a null key is skipped, and when two items share a key the later one wins.
RETURN KEY_BY([{_key: "u1", name: "Ann"}, {_key: "u2", name: "Bo"}], "_key")
{
"u1": {
"_key": "u1",
"name": "Ann"
},
"u2": {
"_key": "u2",
"name": "Bo"
}
}COUNT_BY(arr, x -> key | "path"?) 1.3.0
Counts per key, as {key: count}. Without a key it counts the values themselves — tags, statuses, anything already in an array. For grouping the rows of a query, COLLECT … WITH COUNT INTO is still the tool.
RETURN [COUNT_BY(["sale", "new", "sale"]), COUNT_BY(["apple", "avocado", "kiwi"], w -> LEFT(w, 1))]
[{"new": 1, "sale": 2}, {"a": 2, "k": 1}]MIN_BY(arr, x -> key | "path") 1.2.2
The whole element with the smallest key, not just the key — the cheapest offer, not its price. MAX_BY is the mirror. Both came in 1.2.2.
RETURN MIN_BY([{n: "a", price: 3}, {n: "b", price: 1}, {n: "c"}], "price")
{"n": "b", "price": 1}MODE(arr) 1.3.0
The most frequent value, nulls ignored. On a tie, the value seen first wins.
RETURN MODE([3, 1, 3, 2, 1, 3])
3
PAIRWISE(arr) 1.3.0
Each element with the next one, for gaps and deltas between consecutive values — time between visits, growth between months.
RETURN PAIRWISE([10, 12, 17])
[[10, 12], [12, 17]]
TRANSPOSE(rows) 1.3.0
Swaps rows and columns of an array of arrays. Shorter rows are padded with null.
RETURN TRANSPOSE([[1, 2, 3], [4, 5]])
[[1, 4], [2, 5], [3, null]]
SHUFFLE(arr) 1.3.0
The elements in random order. The result differs on every run.
RETURN SHUFFLE([1, 2, 3, 4, 5])
[2, 4, 1, 5, 3]
What changed? Diffs and object helpers
DIFF(old, new) lists every field that changed, by dotted path, as {old, new}. Pair it with OLD and NEW and an update returns its own audit entry:
UPDATE "c1" WITH {name: "Anne", tier: "gold"} IN customers RETURN DIFF(UNSET(OLD, "_rev", "_updated_at"), UNSET(NEW, "_rev", "_updated_at"))
[
{
"name": {
"new": "Anne",
"old": "Ann"
},
"tier": {
"new": "gold",
"old": null
}
}
]Remove _rev and _updated_at first: both change on every update, so without the UNSET they appear in every diff. A null argument reads as an empty object, so on an insert — where OLD is null — every field is listed.
DIFF(old, new) 1.3.0
Nested objects are compared field by field and reported by path; arrays are compared whole. A missing field reads as null.
RETURN DIFF({name: "Ann", addr: {city: "Paris", zip: "75001"}}, {name: "Anne", addr: {city: "Lyon", zip: "75001"}})
{
"addr.city": {
"new": "Lyon",
"old": "Paris"
},
"name": {
"new": "Anne",
"old": "Ann"
}
}MAP_VALUES(obj, (v, k) -> expr) 1.3.0
Maps each value of an object through a lambda. The key is an optional second parameter.
RETURN MAP_VALUES({a: 1, b: 2}, (v, k) -> CONCAT(k, "=", v * 10))
{"a": "a=10", "b": "b=20"}MAP_KEYS(obj, (k, v) -> expr) 1.3.0
Renames each key. A key mapped to null is dropped; if two keys collide, the later value wins.
RETURN MAP_KEYS({FirstName: "Ann", LastName: "Lee"}, k -> LOWER(k))
{"firstname": "Ann", "lastname": "Lee"}FILTER_KEYS(obj, (k, v) -> cond) 1.3.0
Keeps the entries for which the lambda is true — here, everything but the system fields. The first parameter is the key, the optional second the value.
RETURN FILTER_KEYS({_key: "x", _rev: "1", name: "Ann", score: 3}, k -> !STARTS_WITH(k, "_"))
{"name": "Ann", "score": 3}SET_PATH(obj, path, value) 1.2.2
A copy with a value set deep inside, creating the levels on the way (1.2.2). UNSET_PATH removes one.
RETURN SET_PATH({a: {b: 1}}, "a.c.d", 2)
{"a": {"b": 1, "c": {"d": 2}}}PARSE_URL(url) 1.3.0
The parts of an absolute URL, with the query already decoded into params — a repeated key becomes an array. Null for anything that is not an absolute URL, so it is safe on user data.
RETURN PARSE_URL("https://shop.io:8443/p?id=4&tag=a&tag=b#top")
{
"fragment": "top",
"host": "shop.io",
"params": {
"id": "4",
"tag": [
"a",
"b"
]
},
"password": null,
"path": "/p",
"port": 8443,
"query": "id=4&tag=a&tag=b",
"scheme": "https",
"username": null
}QUERY_STRING(obj | text) 1.3.0
The other direction: an object to a URL-encoded query string (arrays repeat the key, nulls are left out), or a query string back to an object.
RETURN [QUERY_STRING({q: "a b", tag: ["x", "y"], skip: null}), QUERY_STRING("?page=2&sort=name")]
["q=a+b&tag=x&tag=y", {"page": "2", "sort": "name"}]Search that ignores accents
SDBQL compares strings byte by byte, so a search for “helene” never found “Hélène”. UNACCENT removes accents from Latin letters and keeps everything else — case, spaces, other scripts. Apply it on both sides of the comparison:
FOR c IN clients FILTER UNACCENT(LOWER(c.name)) LIKE CONCAT(UNACCENT(LOWER("helene")), "%") RETURN c.name
["Hélène Dupré"]
The filter runs per row; no index serves UNACCENT(…). Fulltext search still does not fold accents either.
UNACCENT(text) 1.3.0
Unlike SLUGIFY, which lowercases and transliterates everything, UNACCENT only touches Latin letters: ß becomes ss, Œ becomes OE, and Cyrillic is left as it is.
RETURN [UNACCENT("Hélène Dupré"), UNACCENT("Straße, Œuvre"), UNACCENT("Москва")]
["Helene Dupre", "Strasse, OEuvre", "Москва"]
SPLIT_PART(text, separator, n) 1.3.0
One field of a delimited string, counting from 1; negative counts from the end, and an empty string past the end instead of an error.
RETURN [SPLIT_PART("2024-03-15", "-", 2), SPLIT_PART("ann@shop.fr", "@", -1), SPLIT_PART("a,b", ",", 5)]
["03", "shop.fr", ""]
HUMAN_BYTES(bytes, binary?, decimals?) 1.3.0
A byte count for display: powers of 1000 by default, powers of 1024 with IEC units when the second argument is true.
RETURN [HUMAN_BYTES(1536000), HUMAN_BYTES(1572864, true), HUMAN_BYTES(512)]
["1.5 MB", "1.5 MiB", "512 B"]
NUMBER_FORMAT(num, decimals?, locale?) 1.2.2
Thousands grouping and locale separators for display (1.2.2): en, fr, de, es, it, nl, pt, de-CH, or your own {decimal, thousands}.
RETURN [NUMBER_FORMAT(1234567.891, 2), NUMBER_FORMAT(1234.5, 2, "de")]
["1,234,567.89", "1.234,50"]
IBAN, card numbers, SIREN and SIRET
Four checksum validators, so bad identifiers can be rejected in a FILTER or flagged in a data-quality query instead of in every client. Spaces are ignored, since people type them; anything that is not a string or a number is simply not valid.
IS_IBAN(val) 1.3.0
A known country, that country’s length, and the ISO 7064 mod-97 check. The second IBAN differs from the first by its last digit.
RETURN [IS_IBAN("FR76 3000 6000 0112 3456 7890 189"), IS_IBAN("FR76 3000 6000 0112 3456 7890 188")]
[true, false]
LUHN(val) 1.3.0
True when a digit string passes the Luhn check — card numbers, IMEI, SIREN/SIRET. Spaces are ignored.
RETURN [LUHN("4539 1488 0343 6467"), LUHN("4539 1488 0343 6468")]
[true, false]
IS_SIREN(val) 1.3.0
True for a valid French SIREN: 9 digits passing Luhn.
RETURN IS_SIREN("732 829 320")
true
IS_SIRET(val) 1.3.0
Fourteen digits passing Luhn — with La Poste’s exception: its establishments (SIREN 356 000 000) are numbered past what Luhn allows and are checked by their digit sum being a multiple of 5, as the second example shows.
RETURN [IS_SIRET("732 829 320 00074"), IS_SIRET("356 000 000 49837")]
[true, true]
Rounding money
ROUND works on the binary value of a number, and 1.005 is stored as 1.00499…, so ROUND(1.005, 2) gives 1. A third argument now picks a rounding mode and rounds the decimal digits you see instead:
ROUND(num, prec?, mode?) mode argument, 1.3.0
"half_up", "half_down", "half_even" (banker’s rounding, which keeps the total of many rounded amounts from drifting upward), "up" and "down" (away from and towards zero), "ceil", "floor". Without a mode, ROUND behaves exactly as before.
RETURN [ROUND(1.005, 2), ROUND(1.005, 2, "half_up"), ROUND(2.5, 0, "half_even"), ROUND(3.5, 0, "half_even")]
[1.0, 1.01, 2.0, 4.0]
Four small arithmetic helpers came with it:
GCD(a, b, …) 1.3.0
Greatest common divisor of integers; signs are ignored.
RETURN GCD(12, 18, 30)
6
LCM(a, b, …) 1.3.0
Least common multiple of integers; 0 if any argument is 0.
RETURN LCM(4, 6)
12
HYPOT(a, b, …) 1.3.0
The Euclidean length √(a² + b² + …), without overflow on large values.
RETURN HYPOT(3, 4)
5.0
CBRT(num) 1.3.0
Cube root, negative numbers included.
RETURN CBRT(-27)
-3.0
One bad row, not a failed query
An import with one malformed date used to fail the whole query. TRY, from 1.2.2, returns a fallback instead:
TRY(expr, fallback?) 1.2.2
It is lazy — the fallback is only evaluated on failure — and catches value errors only: a bad argument, an unreadable date, a failed ASSERT. Permission errors, the query timeout and the row ceiling still stop the query.
RETURN [TRY(DATE_PARSE("nope", "%d/%m/%Y"), "n/a"), TRY(1 + 1, 0)]
["n/a", 2]
FOR x IN anything
Up to 1.2.3, a name right after IN was always read as a collection or a variable, and whatever followed it was a syntax error. So FOR t IN doc.tags — about the most natural thing to write — failed with Unexpected token: Dot, and FOR d IN DATE_SERIES(…) with Unexpected token: LeftParen. The workaround was to wrap the source in parentheses or bind it with LET first.
From 1.3.0 the source can be any expression. A bare name still means a collection or a variable.
| Query | Result |
|---|---|
LET doc = {tags: ["red", "blue"]} FOR t IN doc.tags RETURN t | ["red", "blue"] |
FOR d IN DATE_SERIES("2024-01-01", "2024-01-03", "day") RETURN LEFT(d, 10) | ["2024-01-01", "2024-01-02", "2024-01-03"] |
LET rows = [[1, 2], [3]] FOR x IN rows[0] RETURN x | [1, 2] |
LET n = 3 FOR i IN n..5 RETURN i | [3, 4, 5] |
LET l = [3, 1, 2] FOR x IN l |> SORTED() RETURN x | [1, 2, 3] |
LET m = null FOR x IN m ?? ["fallback"] RETURN x | ["fallback"] |
Upgrading
1.3.0 is a drop-in upgrade from 1.2.x. Existing queries behave as before: ROUND without a mode is unchanged, and the FOR change only makes queries parse that used to be rejected. If you are still on 1.2.2 or older, 1.2.3 also fixed a memory problem worth having: a large bind variable was copied once per row, so a bulk upsert of 20,000 rows could exhaust the server’s memory.
Full signatures and edge cases are in the SDBQL function reference — dates, arrays, objects and validation, text, numbers — and every change is listed in the changelog.