SoliDB Docs
Back to SDBQL Reference

Syntax & Basics

Learn the fundamental SDBQL syntax including query structure, pipeline operator, null coalescing, optional chaining, CASE expressions, template strings, and window functions.

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

|> Pipeline Operator NEW

Chain function calls for readable data transformations. The |> operator passes the left value as the first argument to the right-side function. 

-- String transformations
RETURN "  hello world  " |> TRIM() |> UPPER() |> SPLIT(" ")
-- Result: ["HELLO", "WORLD"]

-- Array operations
RETURN [3, 1, 4, 1, 5] |> UNIQUE() |> SORTED() |> REVERSE() |> FIRST()
-- Result: 5

-- With extra arguments
RETURN 3.14159 |> ROUND(2) |> TO_STRING()
-- Result: "3.14"

Lambda Expressions

Use x -> expression syntax for inline transformations with higher-order functions like FILTER, MAP, ANY, and ALL.

FILTER with Lambda
[1, 2, 3, 4, 5] |> FILTER(x -> x > 2)
-- Result: [3, 4, 5]

-- Complex condition
items |> FILTER(i -> i.price > 50 AND i.stock > 0)
MAP with Lambda
[1, 2, 3] |> MAP(x -> x * 2)
-- Result: [2, 4, 6]

-- Extract fields
users |> MAP(u -> u.name)
ANY / ALL
[1, 2, 3] |> ANY(x -> x > 2)  -- true
[1, 2, 3] |> ALL(x -> x > 0)  -- true
REDUCE
-- Sum: (accumulator, item) -> expr, initial
[1, 2, 3, 4, 5] |> REDUCE((acc, x) -> acc + x, 0)
-- Result: 15

-- Concatenate strings
["a", "b", "c"] |> REDUCE((acc, x) -> CONCAT(acc, x), "")
-- Result: "abc"
Chaining Pipeline Operations
-- Filter, transform, and aggregate in one chain
orders
  |> FILTER(o -> o.status == "pending")
  |> MAP(o -> o.total)
  |> REDUCE((sum, t) -> sum + t, 0)
-- Returns total of pending orders

Supported Functions: FIRST, LAST, LENGTH, REVERSE, SORTED, UNIQUE, FLATTEN, UPPER, LOWER, TRIM, SPLIT, ROUND, ABS, FLOOR, CEIL, TO_STRING, TO_NUMBER, FILTER, MAP, ANY, ALL, FIND, REDUCE

?? Null Coalescing Operator NEW

The ?? operator returns the left operand if it's not null, otherwise evaluates and returns the right operand. Provides a concise way to handle null values. 

-- Basic usage
RETURN null ?? "default"       -- Result: "default"
RETURN "value" ?? "default"    -- Result: "value"

-- Chaining multiple fallbacks
RETURN null ?? null ?? "last"  -- Result: "last"

-- With object fields
FOR doc IN users
  RETURN doc.nickname ?? doc.name ?? "Anonymous"
Difference from COALESCE()

?? is syntax sugar for the COALESCE function but with short-circuit evaluation. 

-- These are equivalent:
doc.field ?? "default"
COALESCE(doc.field, "default")
Non-null Falsy Values

Only null triggers fallback. Other falsy values pass through. 

0 ?? 42         -- Result: 0
"" ?? "default" -- Result: ""
false ?? true   -- Result: false
Combine with Pipeline
-- Get first item or default to 0
RETURN ([] |> FIRST()) ?? 0    -- Result: 0

-- Process with fallback
items |> FILTER(x -> x.active) |> FIRST() ?? { name: "None" }

|| Logical OR Operator NEW

The || operator returns the left operand if it's truthy, otherwise evaluates and returns the right operand. Use this when you want to fall back on any falsy value (null, false, 0, ""). 

-- Basic usage
RETURN null || "default"       -- Result: "default"
RETURN false || "default"      -- Result: "default"
RETURN 0 || 42                 -- Result: 42
RETURN "" || "default"         -- Result: "default"

-- Non-falsy values pass through
RETURN "value" || "default"    -- Result: "value"
RETURN 1 || 42                 -- Result: 1
?? vs || Comparison

Use ?? when only null should trigger fallback. Use || when any falsy value should trigger fallback.

Expression ?? (null coalesce) || (logical OR)
null _ 0 0 0
false _ 0 false 0
0 _ 42 0 42
"" _ "default" "" "default"
"value" _ "default" "value" "value"
Common Use Cases
-- Default for empty strings
RETURN doc.nickname || doc.name || "Anonymous"

-- Default for zero values
RETURN doc.count || 1

-- Use in aggregations
RETURN SUM(FOR i IN items RETURN i.qty) || 0

?. Optional Chaining NEW

The ?. operator safely accesses nested properties. If the base is null or not an object, it returns null instead of throwing an error. 

-- Safe nested access
FOR doc IN users
  RETURN {
    name: doc.name,
    city: doc.address?.city,           -- null if no address
    lat: doc.address?.location?.lat    -- null if address or location missing
  }

-- Chained optional access
doc.profile?.settings?.theme?.color    -- null at any missing level
Without Optional Chaining
-- Verbose null checks
doc.address != null
  ? (doc.address.location != null
      ? doc.address.location.lat
      : null)
  : null
With Optional Chaining
-- Clean and concise
doc.address?.location?.lat
Combine with ?? for Defaults
-- Optional chaining + null coalescing
FOR doc IN users
  RETURN {
    name: doc.name,
    city: doc.address?.city ?? "Unknown",
    theme: doc.profile?.settings?.theme ?? "light"
  }
Use in FILTER
-- Filter safely with optional chaining
FOR doc IN users
  FILTER doc.address?.city == "New York"
  RETURN doc

-- Find users with missing data
FOR doc IN users
  FILTER doc.address?.city == null
  RETURN doc.name

Note: ?. returns null for non-object types (arrays, strings, numbers). For array element access, use regular bracket notation with null checks.

CASE CASE/WHEN Expressions NEW

SQL-style conditional expressions with two forms: Simple CASE for value matching and Searched CASE for condition evaluation.

Simple CASE
-- Match expression against values
CASE doc.category
    WHEN "electronics" THEN "Tech"
    WHEN "books" THEN "Reading"
    WHEN "clothing" THEN "Fashion"
    ELSE "Other"
END
Searched CASE
-- Evaluate conditions
CASE
    WHEN doc.price >= 500 THEN "premium"
    WHEN doc.price >= 100 THEN "mid-range"
    WHEN doc.price >= 50 THEN "budget"
    ELSE "bargain"
END
Practical Example
FOR doc IN products
  RETURN {
    name: doc.name,
    price_tier: CASE
      WHEN doc.price >= 500 THEN "premium"
      WHEN doc.price >= 100 THEN "mid-range"
      ELSE "budget"
    END,
    availability: CASE
      WHEN doc.stock == 0 THEN "Out of Stock"
      WHEN doc.stock < 50 THEN "Low Stock"
      ELSE "In Stock"
    END
  }
Use in LET for Reuse
FOR doc IN products
  LET tier = CASE
    WHEN doc.price >= 100 THEN "high"
    ELSE "low"
  END
  FILTER tier == "high"
  RETURN doc
Custom Sort Order
FOR doc IN tasks
  LET priority = CASE doc.status
    WHEN "urgent" THEN 1
    WHEN "high" THEN 2
    WHEN "normal" THEN 3
    ELSE 4
  END
  SORT priority
  RETURN doc

Note: CASE returns null if no WHEN matches and no ELSE is provided. WHEN clauses are evaluated in order - first match wins.

$"" String Interpolation NEW

Template strings with ${expression} syntax for cleaner string building. Use $"..." or $'...' prefix.

Before (Verbose)
RETURN CONCAT("Hello, ", doc.name, "! You have ", TO_STRING(doc.points), " points.")
After (Clean)
RETURN $"Hello, ${doc.name}! You have ${doc.points} points."
Expressions Inside ${...}
FOR doc IN products
  RETURN {
    -- Simple field access
    greeting: $"Welcome, ${doc.name}!",

    -- Arithmetic expressions
    total: $"Total: $$${doc.price * doc.qty}",

    -- Function calls
    formatted: $"Name: ${UPPER(doc.name)}",

    -- Ternary expressions
    status: $"Status: ${doc.active ? 'Active' : 'Inactive'}",

    -- Optional chaining
    city: $"City: ${doc.address?.city ?? 'Unknown'}"
  }
Type Coercion
-- Numbers, booleans, null auto-convert
$"Count: ${42}"         -- "Count: 42"
$"Active: ${true}"      -- "Active: true"
$"Value: ${null}"       -- "Value: null"

-- Arrays and objects serialize to JSON
$"Tags: ${['a','b']}"   -- 'Tags: ["a","b"]'
Escape Sequences
-- Literal dollar sign
$"Price: $$50"          -- "Price: $50"

-- Literal ${
$"Code: \${x}"          -- "Code: ${x}"

-- Standard escapes
$"Line1\nLine2"         -- newline
$"Col1\tCol2"           -- tab

Note: String interpolation is secure - expressions are parsed at compile time as AST nodes, not evaluated as strings at runtime. User data cannot inject code.

OVER Window Functions NEW

Window functions perform calculations across rows related to the current row without collapsing them. Use OVER clause with optional PARTITION BY and ORDER BY to define the window. 

-- Row numbering
FOR doc IN sales
  RETURN {
    date: doc.date,
    row_num: ROW_NUMBER() OVER (ORDER BY doc.date)
  }

-- Partitioned row numbers (restart per region)
FOR doc IN sales
  RETURN {
    region: doc.region,
    row_in_region: ROW_NUMBER() OVER (PARTITION BY doc.region ORDER BY doc.date)
  }

-- Running total
FOR doc IN sales
  RETURN {
    date: doc.date,
    amount: doc.amount,
    running_total: SUM(doc.amount) OVER (ORDER BY doc.date)
  }
Ranking Functions
-- ROW_NUMBER: Sequential numbers
ROW_NUMBER() OVER (ORDER BY score DESC)
-- Result: 1, 2, 3, 4, 5...

-- RANK: Same rank for ties, gaps after
RANK() OVER (ORDER BY score DESC)
-- Scores: 100, 100, 90 -> Ranks: 1, 1, 3

-- DENSE_RANK: Same rank for ties, no gaps
DENSE_RANK() OVER (ORDER BY score DESC)
-- Scores: 100, 100, 90 -> Ranks: 1, 1, 2
Navigation Functions
-- LAG: Access previous row value
LAG(doc.amount) OVER (ORDER BY doc.date)

-- LEAD: Access next row value
LEAD(doc.amount) OVER (ORDER BY doc.date)

-- FIRST_VALUE: First value in partition
FIRST_VALUE(doc.amount) OVER (
  PARTITION BY doc.region ORDER BY doc.date
)

-- LAST_VALUE: Last value in partition
LAST_VALUE(doc.amount) OVER (
  PARTITION BY doc.region ORDER BY doc.date
)
Running Aggregates

Use aggregate functions with OVER for cumulative calculations.

FOR doc IN transactions
  FILTER doc.account_id == "acc123"
  SORT doc.date
  RETURN {
    date: doc.date,
    amount: doc.amount,
    running_sum: SUM(doc.amount) OVER (ORDER BY doc.date),
    running_avg: AVG(doc.amount) OVER (ORDER BY doc.date),
    running_count: COUNT(doc._key) OVER (ORDER BY doc.date),
    running_min: MIN(doc.amount) OVER (ORDER BY doc.date),
    running_max: MAX(doc.amount) OVER (ORDER BY doc.date)
  }
Practical Example: Compare to Previous
-- Calculate day-over-day change
FOR doc IN daily_sales
  SORT doc.date
  LET prev = LAG(doc.revenue) OVER (ORDER BY doc.date)
  RETURN {
    date: doc.date,
    revenue: doc.revenue,
    prev_revenue: prev,
    change: prev != null ? doc.revenue - prev : null,
    pct_change: prev != null ? ROUND((doc.revenue - prev) / prev * 100, 2) : null
  }

Supported Functions: ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, FIRST_VALUE, LAST_VALUE, SUM, AVG, COUNT, MIN, MAX

JOIN JOIN Operations NEW

Combine data from multiple collections efficiently using JOIN, LEFT/RIGHT JOIN, and FULL OUTER JOIN. SDBQL uses document-oriented join semantics where matching documents are grouped into arrays. 

-- Basic INNER JOIN
FOR user IN users
  JOIN orders ON user._key == orders.user_key
  RETURN {
    user: user.name,
    orders: orders  -- matched orders array
  }

-- LEFT JOIN (include users without orders)
FOR user IN users
  LEFT JOIN orders ON user._key == orders.user_key
  RETURN {
    user: user.name,
    orders: orders  -- empty array if no matches
  }

-- RIGHT JOIN (all orders with matching users)
FOR user IN users
  RIGHT JOIN orders ON user._key == orders.user_key
  RETURN {
    order: orders._key,
    user: user  -- array containing the matched user(s)
  }

-- FULL OUTER JOIN (union of all rows)
FOR user IN users
  FULL OUTER JOIN orders ON user._key == orders.user_key
  RETURN {
    user_name: user?.name,
    order_id: orders?._key
  }
Key Features
  • Support for INNER, LEFT, RIGHT, and FULL joins
  • Join condition using ON clause
  • Multiple sequential joins supported
  • Document-oriented results (nested arrays)
Complex Example
FOR u IN users
  JOIN orders ON u._key == orders.user_key
  LEFT JOIN reviews ON u._key == reviews.user
  RETURN {
    name: u.name,
    spent: SUM(orders[*].total),
    review_count: LENGTH(reviews)
  }

Note: The variable name for the joined collection is automatically derived from the collection name.

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 }

BULK Bulk Operations NEW

Perform efficient batch operations like INSERT, UPDATE, DELETE, and UPSERT on collections. 

-- Bulk INSERT
INSERT { name: "New User 1", email: "[email protected]" } INTO users
INSERT { name: "New User 2", email: "[email protected]" } INTO users

-- Bulk UPDATE
FOR u IN users
  FILTER u.status == "pending"
  UPDATE u WITH { status: "active", updated_at: DATE_NOW() } IN users

-- Bulk DELETE
FOR o IN old_orders
  FILTER o.date < DATE_SUBTRACT(DATE_NOW(), 1, "year")
  REMOVE o IN old_orders

-- Bulk UPSERT (Update or Insert)
UPSERT { email: "[email protected]" }
  INSERT { name: "Example User", email: "[email protected]", created_at: DATE_NOW() }
  UPDATE { last_login: DATE_NOW() }
  IN users
Key Features
  • High-performance batch operations
  • Atomic operations for data consistency
  • Flexible filtering for targeted changes
  • UPSERT for conditional inserts/updates
Return Modified Documents
FOR u IN users
  FILTER u.status == "new"
  UPDATE u WITH { status: "active" } IN users
  RETURN NEW

Use RETURN NEW or RETURN OLD to get the state of documents before or after modification.

SDBQL Overview Operators