Back to Documentation

SDBQL Reference

Master the SoliDB Query Language (SDBQL) in SoliDB. A powerful, declarative language for querying and manipulating your JSON documents.

Basic Syntax

SDBQL queries follow a declarative structure that reads like a sentence. 

LET variable = expression
FOR variable IN collection
  FILTER expression
  SORT field [ASC|DESC]
  LIMIT [offset,] count
  RETURN expression

LET Variable Assignment

Binds values or subquery results to variables.

FOR Iteration

Iterates over collections or arrays.

FILTER Filtering

Restricts results based on conditions.

RETURN Projection

Constructs the result documents.

.. Range Expressions

Create numeric arrays using the range operator start..end (inclusive on both ends).

1..5 → [1, 2, 3, 4, 5]

Basic Range 

-- Returns [1, 2, 3, 4, 5]
RETURN 1..5

-- Negative numbers
RETURN -2..2  // [-2, -1, 0, 1, 2]

In FOR Loop 

FOR i IN 1..10
  RETURN i * 2

-- With expressions
LET n = 5
FOR i IN 1..n
  RETURN i

Batch Insert with Range 

FOR i IN 1..100
  INSERT { index: i, name: CONCAT("Item", i) } INTO items
  RETURN i

LET & Subqueries

Correlated Subqueries

Use LET inside a FOR loop to perform powerful joins and aggregations. 

FOR u IN users
  -- Subquery accessing outer variable 'u'
  LET userOrders = (
    FOR o IN orders
    FILTER o.user == u.name
    RETURN o
  )
  RETURN { name: u.name, orders: userOrders }

Aggregation with Subqueries 

FOR u IN users
  LET totalSpent = SUM((
    FOR o IN orders
    FILTER o.user == u.name
    RETURN o.amount
  ))
  FILTER totalSpent > 100
  RETURN { name: u.name, spent: totalSpent }

Operators

Comparison

== != < <= > >=

Logical

AND OR NOT

Arithmetic

+ - * /

Bind Variables

SECURITY ESSENTIAL

Prevent Injection Attacks

Never concatenate user input directly into query strings. Always use bind variables (@variable) to safely substitute values.

Query 

FOR u IN users
  FILTER u.name == @name
  AND u.age >= @minAge
  RETURN u

Bind Vars 

{
  "name": "Alice",
  "minAge": 25
}

Dynamic Field Access

Use bracket notation with bind variables for dynamic field names.

FILTER doc[@fieldName] == @value

Data Modification

SDBQL supports data modification operations for inserting and updating documents directly within queries.

INSERT Insert Documents

Insert new documents into a collection.

INSERT document INTO collection

Simple Insert 

LET nums = [1, 2, 3]
FOR i IN nums
  INSERT { value: i } INTO items
  RETURN i

With Computed Fields 

FOR i IN 1..5
  INSERT {
    name: CONCAT("User", i),
    index: i
  } INTO users
  RETURN i

UPDATE Update Documents

Update existing documents in a collection by merging changes into existing fields.

UPDATE document WITH { changes } IN collection

Merge Behavior

UPDATE merges the changes into the existing document. Fields not specified in the changes object are preserved.

Update Single Document 

FOR doc IN users
  FILTER doc.name == "Alice"
  UPDATE doc WITH {
    status: "premium"
  } IN users
  RETURN doc.name

Update Multiple Fields 

FOR doc IN users
  FILTER doc.age >= 30
  UPDATE doc WITH {
    status: "vip",
    level: 5,
    verified: true
  } IN users
  RETURN doc._key

With Computed Values 

FOR doc IN users
  FILTER doc.city == "Paris"
  UPDATE doc WITH {
    fullName: CONCAT(doc.name, " from ", doc.city),
    region: "Europe"
  } IN users
  RETURN doc.name

With Bind Variables 

FOR doc IN users
  FILTER doc.name == @name
  UPDATE doc WITH { points: @points } IN users
  RETURN doc.name
{
  "name": "Alice",
  "points": 100
}

REMOVE Delete Documents

Remove documents from a collection.

REMOVE document IN collection

Caution

REMOVE permanently deletes documents. Always use FILTER to target specific documents, or you'll delete all documents in the collection.

Remove by Key (Single) 

FOR doc IN users
  FILTER doc._key == "alice"
  REMOVE doc IN users
  RETURN doc._key

Remove by Condition (Multiple) 

FOR doc IN users
  FILTER doc.city == "Paris"
  REMOVE doc IN users
  RETURN doc.name

Remove All (Use with Caution!) 

FOR doc IN users
  REMOVE doc IN users
  RETURN doc._key

Graph Queries

SoliDB supports native graph traversals and shortest path algorithms using dedicated keywords.

TRAVERSAL Graph Traversal

Traverse the graph starting from a vertex.

FOR vertex[, edge] IN [min..max] DIRECTION startVertex edgeCollection
OUTBOUND
_from → _to
INBOUND
_to ← _from
ANY
Either direction

Direct (1 Hop) 

FOR v IN OUTBOUND "users/alice" follows
  RETURN v.name

Variable Depth (1..2 Hops) 

FOR v, e IN 1..2 ANY "users/alice" follows
  RETURN { user: v.name, type: e.type }

SHORTEST_PATH Shortest Path

Find the shortest path between two vertices.

FOR vertex[, edge] IN SHORTEST_PATH start TO end DIRECTION edgeCollection

Find Path 

FOR v, e IN OUTBOUND SHORTEST_PATH
  "users/alice" TO "users/charlie"
  follows
  RETURN { vertex: v.name, edge: e }

Functions

Numeric

ABS(number)

Returns the absolute value. 

RETURN ABS(-5) // 5
CEIL(number)

Rounds up to nearest integer. 

RETURN CEIL(4.2) // 5
FLOOR(number)

Rounds down to nearest integer. 

RETURN FLOOR(4.8) // 4
ROUND(num, prec?)

Rounds to specified precision. 

RETURN ROUND(3.14159, 2) // 3.14
RANDOM()

Returns a random float between 0 and 1. 

RETURN RANDOM() // 0.54321...

String

CONCAT(str1, ...)

Concatenates strings.

RETURN CONCAT("A", "B") // "AB"
CONCAT_SEPARATOR(sep, arr)

Joins array with separator. 

RETURN CONCAT_SEPARATOR(",", ["A","B"])
LENGTH(str)

Returns string length.

RETURN LENGTH("Hello") // 5
SUBSTRING(str, start, len?)

Extracts substring.

RETURN SUBSTRING("Hello", 0, 2) // "He"
LOWER(str) / UPPER(str)

Case conversion.

RETURN LOWER("Hi") // "hi"
LEVENSHTEIN(s1, s2)

Calculates edit distance. 

RETURN LEVENSHTEIN("foo", "bar")
REGEX_REPLACE(text, pattern, replacement, caseInsensitive?)

Replace occurrences of a pattern in a string. 

RETURN REGEX_REPLACE("the quick brown fox", "the (.*) fox", "a $1 dog")
// "a quick brown dog"
CONTAINS(text, search, returnIndex?)

Checks if search string is in text. Returns boolean or index. 

RETURN CONTAINS("foobar", "bar") // true
RETURN CONTAINS("foobar", "bar", true) // 3
SUBSTITUTE(val, search, replace, limit?)

Replaces occurrences of search with replace. 

RETURN SUBSTITUTE("foobar", "foo", "baz") // "bazbar"
RETURN SUBSTITUTE("banana", "a", "o", 2) // "bonona"
RETURN SUBSTITUTE("hello", {"l": "x", "o": "y"}) // "hexxiy"
SPLIT(val, sep, limit?)

Splits string into array. 

RETURN SPLIT("foo-bar-baz", "-") // ["foo", "bar", "baz"]
RETURN SPLIT("foo-bar-baz", "-", 2) // ["foo", "bar-baz"]
RETURN SPLIT("foo-bar-baz", "-", -2) // ["foo-bar", "baz"]
TRIM(val, type?/chars?)

Trims whitespace or chars. type: 0=both, 1=left, 2=right. 

RETURN TRIM(" foo ") // "foo"
RETURN TRIM(" foo ", 1) // "foo "
RETURN TRIM("--foo--", "-") // "foo"
LTRIM(val, chars?)

Trims from start.

RETURN LTRIM(" foo ") // "foo "
RTRIM(val, chars?)

Trims from end.

RETURN RTRIM(" foo ") // " foo"
JSON_PARSE(text)

Parses JSON string to value. Returns NULL on error. 

RETURN JSON_PARSE("{\"a\":1}") // {a: 1}
JSON_STRINGIFY(value)

Serializes value to JSON string. 

RETURN JSON_STRINGIFY({a: 1}) // "{\"a\":1}"

Type Casting Functions

TO_BOOL(value)

Casts value to boolean.

RETURN TO_BOOL(null) // false
RETURN TO_BOOL(1) // true
RETURN TO_BOOL(0) // false
TO_NUMBER(value)

Casts value to number.

RETURN TO_NUMBER("123") // 123
RETURN TO_NUMBER(true) // 1
RETURN TO_NUMBER("foo") // 0
TO_STRING(value)

Casts value to string.

RETURN TO_STRING(123) // "123"
RETURN TO_STRING(true) // "true"
RETURN TO_STRING(null) // ""
TO_ARRAY(value)

Casts value to array.

RETURN TO_ARRAY(null) // []
RETURN TO_ARRAY("foo") // ["foo"]
RETURN TO_ARRAY({a:1}) // [1]

Date

DATE_NOW()

Returns current Unix timestamp (ms). 

RETURN DATE_NOW() // 1733234387000
DATE_ISO8601(timestamp)

Converts timestamp to ISO 8601 string. 

RETURN DATE_ISO8601(1733234387000)
// "2025-12-03T13:59:47.000Z"
DATE_TIMESTAMP(date)

Converts ISO 8601 string to timestamp (ms). 

RETURN DATE_TIMESTAMP("2025-12-03T13:59:47.000Z")
// 1764770387000
DATE_ISOWEEK(date)

Returns ISO 8601 week number (1-53). 

RETURN DATE_ISOWEEK("2025-06-15T14:30:45Z")
// 24
DATE_DAYOFYEAR(date, tz?)

Returns day of year (1-366). Optional timezone. 

RETURN DATE_DAYOFYEAR("2025-06-15T14:30:45Z")
// 166
DATE_DAYS_IN_MONTH(date, tz?)

Returns days in the month (28-31). Optional timezone. 

RETURN DATE_DAYS_IN_MONTH("2024-02-15T00:00:00Z")
// 29 (leap year)
DATE_TRUNC(date, unit, timezone?)

Truncates date to specified unit. Returns ISO 8601 string.

Parameters
  • date - timestamp (ms) or ISO 8601 string
  • unit - y/year, m/month, d/day, h/hour, i/minute, s/second, f/millisecond
  • timezone - optional IANA timezone (e.g. "America/New_York")
Examples 
DATE_TRUNC("2025-06-15T14:30:45Z", "day")
// "2025-06-15T00:00:00.000Z"

DATE_TRUNC("2025-06-15T20:30:00Z", "day", "America/New_York")
// "2025-06-15T04:00:00.000Z"
DATE_FORMAT(date, format, timezone?)

Formats date according to format string (strftime-style).

Format Specifiers
%Y - year (2025) %m - month (01-12) %d - day (01-31) %H - hour 24h (00-23) %M - minute (00-59) %S - second (00-59) %I - hour 12h (01-12) %p - AM/PM %A - weekday (Sunday) %B - month (June) %j - day of year %V - ISO week
Examples 
DATE_FORMAT("2025-06-15T14:30:45Z", "%Y-%m-%d")
// "2025-06-15"

DATE_FORMAT("2025-06-15T14:30:45Z", "%I:%M %p")
// "02:30 PM"

DATE_FORMAT("2025-06-15T14:30:00Z", "%H:%M", "America/New_York")
// "10:30"
DATE_ADD(date, amount, unit, timezone?)

Add or subtract a specified amount of time to/from a date. Returns ISO 8601 string.

Parameters
  • date - timestamp (ms) or ISO 8601 string
  • amount - number to add (positive) or subtract (negative)
  • unit - y/year, m/month, w/week, d/day, h/hour, i/minute, s/second, f/millisecond
  • timezone - optional IANA timezone (e.g. "America/New_York")
Examples 
DATE_ADD("2025-06-15T14:30:45Z", 3, "months")
// "2025-09-15T14:30:45.000Z"

DATE_ADD("2025-06-15T14:30:45Z", -7, "days")
// "2025-06-08T14:30:45.000Z"

DATE_ADD(1733234387000, 2, "hours")
// "2024-12-03T15:59:47.000Z"
DATE_SUBTRACT(date, amount, unit, timezone?)

Subtract a specified amount of time from a date. Convenience wrapper for DATE_ADD with negated amount. Returns ISO 8601 string.

Parameters
  • date - timestamp (ms) or ISO 8601 string
  • amount - number to subtract (always positive)
  • unit - y/year, m/month, w/week, d/day, h/hour, i/minute, s/second, f/millisecond
  • timezone - optional IANA timezone (e.g. "America/New_York")
Examples 
DATE_SUBTRACT("2025-06-15T14:30:45Z", 7, "days")
// "2025-06-08T14:30:45.000Z"

DATE_SUBTRACT("2025-06-15T14:30:45Z", 3, "months")
// "2025-03-15T14:30:45.000Z"

DATE_SUBTRACT(1733234387000, 1, "day")
// "2024-12-02T13:59:47.000Z"
DATE_DIFF(date1, date2, unit, asFloat?, timezone1?, timezone2?)

Calculate the difference between two dates in a given time unit. Returns negative if date2 is before date1.

Parameters
  • date1 - timestamp (ms) or ISO 8601 string
  • date2 - timestamp (ms) or ISO 8601 string
  • unit - y/year, m/month, w/week, d/day, h/hour, i/minute, s/second, f/millisecond
  • asFloat - optional, preserve decimal places (default: false)
  • timezone1 - optional IANA timezone for date1
  • timezone2 - optional IANA timezone for date2
Examples 
DATE_DIFF("2025-06-01T00:00:00Z", "2025-06-11T00:00:00Z", "days")
// 10

DATE_DIFF("2025-06-15T00:00:00Z", "2025-06-15T12:00:00Z", "days", true)
// 0.5

DATE_DIFF("2025-06-15T00:00:00Z", "2025-06-10T00:00:00Z", "days")
// -5 (negative: date2 before date1)

Geospatial

DISTANCE(lat1, lon1, lat2, lon2)

Distance in meters between coordinates.

GEO_DISTANCE(p1, p2)

Distance in meters between GeoPoints.

Fulltext Search

FULLTEXT(col, field, query, dist?)

Fuzzy search using n-gram indexing. 

LET matches = FULLTEXT("articles", "title", "rust", 2)
FOR m IN matches RETURN m.doc
BM25(field, query)

BM25 relevance scoring for ranking search results. Returns a numeric score that can be used in SORT clauses.

BM25 Algorithm

BM25 (Best Matching 25) is a probabilistic ranking function that provides more sophisticated relevance scoring than n-gram similarity. It considers term frequency, document length, and inverse document frequency.

Basic Scoring 

FOR doc IN articles
  RETURN {
    title: doc.title,
    score: BM25(doc.content, "machine learning")
  }

Sort by Relevance 

FOR doc IN articles
  SORT BM25(doc.content, "rust database") DESC
  LIMIT 10
  RETURN doc

Combined with Filters 

FOR doc IN articles
  FILTER doc.published == true
  SORT BM25(doc.content, "query optimization") DESC
  LIMIT 5
  RETURN {title: doc.title, score: BM25(doc.content, "query optimization")}

Aggregation

Basic Aggregations

SUM(arr)

Sum of all values in the array. 

RETURN SUM([1, 2, 3, 4]) // 10
AVG(arr)

Average of all values.

RETURN AVG([1, 2, 3, 4]) // 2.5
MIN(arr)

Smallest value in the array. 

RETURN MIN([5, 1, 9]) // 1
MAX(arr)

Largest value in the array. 

RETURN MAX([5, 1, 9]) // 9
COUNT(arr)

Number of elements in the array. 

RETURN COUNT([1, 2, 3]) // 3
COUNT_DISTINCT(arr)

Number of unique elements. 

RETURN COUNT_DISTINCT([1, 2, 2, 3]) // 3
MEDIAN(arr)

Median value of the array. 

RETURN MEDIAN([1, 5, 10]) // 5
PERCENTILE(arr, p)

Returns the p-th percentile (0-100). 

RETURN PERCENTILE([1..100], 95) // 95

Statistical Functions

VARIANCE(arr)

Population variance.

RETURN VARIANCE([1, 2, 3, 4, 5]) // 2
VARIANCE_SAMPLE(arr)

Sample variance.

RETURN VARIANCE_SAMPLE([1, 2, 3, 4, 5]) // 2.5
STDDEV(arr)

Sample standard deviation. 

RETURN STDDEV([1, 2, 3, 4, 5]) // 1.581...
STDDEV_POPULATION(arr)

Population standard deviation. 

RETURN STDDEV_POPULATION([1, 2, 3, 4, 5]) // 1.414...

Aggregation Example 

FOR u IN users
  LET amounts = (FOR o IN orders FILTER o.user == u._key RETURN o.amount)
  RETURN {
    user: u.name,
    total: SUM(amounts),
    avg: ROUND(AVG(amounts), 2),
    count: COUNT(amounts),
    median: MEDIAN(amounts),
    stddev: ROUND(STDDEV(amounts), 2)
  }

Array

Access Functions

Access Functions

FIRST(arr)

Returns the first element. 

RETURN FIRST([5, 1, 9]) // 5
COLLECTION_COUNT(coll)

Returns the number of documents in a collection. 

RETURN COLLECTION_COUNT("users") // 100
LAST(arr)

Returns the last element. 

RETURN LAST([5, 1, 9]) // 9
NTH(arr, n)

Returns element at index n (0-based). 

RETURN NTH([5, 1, 9], 1) // 1
SLICE(arr, start, len?)

Extracts a portion of the array. 

RETURN SLICE([1, 2, 3, 4, 5], 1, 3) // [2, 3, 4]

Transformation Functions

UNIQUE(arr)

Removes duplicate values. 

RETURN UNIQUE([1, 2, 2, 3]) // [1, 2, 3]
SORTED(arr)

Sorts the array in ascending order. 

RETURN SORTED([3, 1, 2]) // [1, 2, 3]
SORTED_UNIQUE(arr)

Sorts and removes duplicates. 

RETURN SORTED_UNIQUE([3, 1, 2, 2]) // [1, 2, 3]
REVERSE(arr)

Reverses the array order. 

RETURN REVERSE([1, 2, 3]) // [3, 2, 1]
FLATTEN(arr, depth?)

Flattens nested arrays. Depth defaults to 1. 

RETURN FLATTEN([[1, 2], [3]], 1) // [1, 2, 3]

Combination Functions

PUSH(arr, elem)

Appends an element to the array. 

RETURN PUSH([1, 2], 3) // [1, 2, 3]
APPEND(arr1, arr2)

Concatenates two arrays.

RETURN APPEND([1], [2, 3]) // [1, 2, 3]
UNION(arr1, arr2)

Union of arrays (produces unique values). 

RETURN UNION([1, 2], [2, 3]) // [1, 2, 3]
INTERSECTION(arr1, arr2...)

Returns common elements between arrays. 

RETURN INTERSECTION([1, 2], [2, 3]) // [2]
MINUS(arr1, arr2)

Returns elements in arr1 not in arr2. 

RETURN MINUS([1, 2, 3], [2]) // [1, 3]

Search Functions

POSITION(arr, elem)

Returns true if element exists, false otherwise. 

RETURN POSITION([10, 20], 20) // true
CONTAINS_ARRAY(arr, elem)

Legacy alias for POSITION. 

RETURN CONTAINS_ARRAY([1, 2], 3) // false

Array Examples

-- Combine and deduplicate tags
LET tags1 = ["rust", "database"]
LET tags2 = ["database", "nosql"]
RETURN UNION(tags1, tags2)  -- ["rust", "database", "nosql"]

-- Get top 3 values from sorted array
LET values = [5, 2, 8, 1, 9, 3]
RETURN SLICE(SORTED(values), 3, 3)  -- [5, 8, 9] (last 3)

-- Flatten nested arrays
LET nested = [[1, 2], [3, [4, 5]]]
RETURN FLATTEN(nested, 2)  -- [1, 2, 3, 4, 5]

Object & Array

MERGE(obj1, obj2)

Shallow merge of objects. 

RETURN MERGE({a:1}, {b:2})
LENGTH(val)

Count elements in array/object. 

RETURN LENGTH([1,2,3]) // 3
HAS(doc, attr)

Checks if document contains attribute. 

FILTER HAS(doc, "email")
KEEP(doc, attr...)

Keep only specified attributes. 

RETURN KEEP(doc, "name", "email")
ATTRIBUTES(doc, removeInternal?, sort?)

Top-level attribute keys of the document. 

RETURN ATTRIBUTES(doc, true)
VALUES(doc, removeInternal?)

Returns top-level attribute values. 

RETURN VALUES(doc, true)
UNSET(doc, attr...)

Removes specified attributes. 

RETURN UNSET(doc, "password")

Miscellaneous

UUIDV4()

Generates a random v4 UUID. 

RETURN UUIDV4() // "c4d3..."
UUIDV7()

Generates a time-ordered v7 UUID. 

RETURN UUIDV7() // "018..."

Collection

COLLECTION_COUNT(name)

Returns the number of documents in a collection. Efficient metadata lookup without iterating documents. 

RETURN COLLECTION_COUNT("users") // 42

Usage Example

Get document counts for multiple collections: 

RETURN {
  users: COLLECTION_COUNT("users"),
  orders: COLLECTION_COUNT("orders"),
  products: COLLECTION_COUNT("products")
}