Overview

Common Table Expressions (CTEs) allow you to define named temporary result sets that can be referenced within your query. They improve query readability and enable recursive data structures.

CTEs are defined using the WITH clause and can be referenced like collections using FOR ... IN.

Simple CTE

-- Define a CTE to get active users
WITH active_users AS (
  FOR u IN users
    FILTER u.active == true
    RETURN u
)
-- Now iterate over the CTE like a collection
FOR u IN active_users
  SORT u.name ASC
  RETURN u

Multiple CTEs

-- Get high-value orders with customer details
WITH 
  high_value_orders AS (
    FOR o IN orders
      FILTER o.total > 1000
      RETURN o
  ),
  vip_customers AS (
    FOR c IN customers
      FILTER c.tier == 'vip'
      RETURN c
  )
-- Join the CTEs
FOR o IN high_value_orders
  FOR c IN vip_customers
    FILTER o.customer_id == c._key
    RETURN {order: o, customer: c}

CTE with Column Names

WITH summary(id, name, total) AS (
  FOR o IN orders
    FILTER o.status == 'completed'
    RETURN {id: o._key, name: o.customer_name, total: o.total}
)
FOR s IN summary
  SORT s.total DESC
  RETURN s

CTEs vs LET

Recursive CTE

WITH RECURSIVE walks hierarchical data (org charts, category trees, bill of materials, transitive closures) in pure SDBQL. The body must have the shape <anchor query> UNION ALL <recursive step>. The anchor runs once; then the step runs repeatedly until it stops producing rows.

Inside the step, the CTE name is bound to the rows produced by the previous iteration — use it to expand one level at a time. Recursion is capped at 1,000 iterations and 1M rows.

WITH RECURSIVE reports AS (
  -- Anchor: the root of the hierarchy
  FOR e IN employees
    FILTER e._key == 'alice'
    RETURN e._key
  UNION ALL
  -- Step: find direct reports of the previous level
  FOR m IN employees
    FILTER m.manager IN reports
    RETURN m._key
)
FOR x IN reports
  RETURN x

• RECURSIVE applies to every CTE in the WITH list (standard SQL semantics)
• Steps must be joined with UNION ALL; other set operators are rejected on a recursive CTE body
• The anchor and each step are full query blocks: they may have their own LETs, SORT/LIMIT, and nested WITH
• A step that never stops producing new rows ends with an iteration-limit error rather than hanging. Cyclic data (a → b → a) is exactly that case: filter out rows you have already visited in the step, or carry a depth and stop at it

Syntax

WITH [RECURSIVE]
  cte_name [(col1, col2, ...)] AS ( query ),
  cte_name2 [(col1, col2, ...)] AS ( query )
query_body

• cte_name - Identifier for the CTE
• (col1, col2, ...) - Optional column names
• query - Any valid SDBQL query
• query_body - Main query that can reference CTEs