Analytics & Columnar Stores

Rich Aggregation (Document Models)

Group on one or more fields with group_by and compute several named aggregates at once with aggregate:

rows = Order
  .where({ "status": "paid" })
  .group_by(["country", "plan"])
  .aggregate({ "total": ["sum", "amount"], "avg_age": ["avg", "age"], "n": ["count"] })
  .having("total > @min", { "min": 1000 })
  .order("total", "desc")
  .limit(20)
  .all
# => [{ "country": "FR", "plan": "pro", "total": 5300, "avg_age": 34.2, "n": 12 }, ...]

• group_by takes a field name or an array of field names. Each result row is a plain hash carrying the group fields plus your aggregate aliases.
• The aggregate spec maps alias: [func, field] — or alias: ["count"] for a plain row count.
• In grouped mode, order must name a group field or an aggregate alias; limit / offset compose as usual.

Aggregate functions

Shorthand forms

# 1-arg group_by: implicit count per group, aliased "n"
User.group_by("role").all
# => [{ "role": "admin", "n": 3 }, { "role": "member", "n": 240 }]

# Ungrouped aggregate: the whole match collapses to one row — chain .first
totals = Order.aggregate({ "total": ["sum", "amount"], "n": ["count"] }).first

# Statistical terminals — like sum/avg, chain .first
Order.median("amount").first
Order.where({ "status": "paid" }).stddev("amount").first
Order.variance("amount").first
Order.count_distinct("customer_id").first

# Legacy 3-arg form: unchanged, returns [{group, result}]
User.group_by("country", "sum", "balance").all

Filtering groups with having

having(expr, binds?) filters after the COLLECT, over the bare aggregate aliases and group fields (no doc. prefix):

Order
  .group_by("country")
  .aggregate({ "total": ["sum", "amount"], "n": ["count"] })
  .having("total > @min AND n >= @orders", { "min": 1000, "orders": 5 })
  .all

Soft delete

The new grouped queries (group_by + aggregate) respect the model's soft-delete scope — deleted rows are excluded by default, and with_deleted / only_deleted compose. One honest inconsistency: the legacy three-argument form never applied the soft-delete filter, and still doesn't (kept unchanged for backward compatibility).

Window functions: raw-SDBQL escape hatch

ROW_NUMBER() / LAG() / OVER (...) are not exposed through the QueryBuilder. Drop down to a raw query (db.query in a script/migration, or an @sdbql{} block) when you need them:

db = Solidb(env("SOLIDB_HOST"), env("SOLIDB_DATABASE"))
rows = db.query("
  FOR o IN orders
    RETURN { country: o.country, amount: o.amount,
             rank: ROW_NUMBER() OVER (PARTITION BY o.country ORDER BY o.amount DESC) }
")

Columnar Models

Columnar stores are a separate storage engine inside SolidB: data is laid out by column rather than by document, which makes appends cheap and large scans/aggregations fast. They are not document collections — they live behind their own HTTP API, are not visible to SDBQL FOR loops, and have no document CRUD.

class PageView < Model
  columnar compression: "lz4"        # optional options; bare `columnar` works
  column "url", "string"
  column "visited_at", "timestamp"
  column "duration_ms", "int", nullable: true
  column "country", "string", indexed: true
end

• columnar marks the model as a columnar store. Options: compression: — "lz4" (the default) or "none".
• column name, type declares a typed column. nullable: true allows nils; indexed: true creates the default (sorted) column index.

Column types

Inserting rows (insert_rows)

PageView.insert_rows([
  { "url": "/", "visited_at": "2026-07-05T10:00:00Z", "duration_ms": 12, "country": "FR" },
  { "url": "/pricing", "visited_at": "2026-07-05T10:00:03Z", "duration_ms": 48, "country": "DE" }
])
# => { "inserted": 2, "ids": [...] }

In dev the store is auto-created (with your declared columns) on first use, like document collections.

Aggregating

aggregate(field, op, options?) returns a scalar, or one row per group with a group_by option. Ops: count / sum / avg / min / max / count_distinct.

PageView.aggregate("duration_ms", "avg")                               # => 27.4 (scalar)
PageView.aggregate("duration_ms", "avg", { "group_by": ["country"] })
# => [{ "country": "FR", "value": 12.0 }, { "country": "DE", "value": 48.0 }]
PageView.count

Querying rows (query)

PageView.query({
  "columns": ["url", "duration_ms"],
  "filter": { "column": "country", "op": "eq", "value": "FR" },
  "limit": 100
})

Column indexes

Kinds: sorted (the default) | hash | bitmap | minmax | bloom. Rule of thumb: sorted for ranges and equality, hash for pure equality, bitmap for low-cardinality columns (country, status), minmax for block pruning on range scans, bloom for fast negative membership tests.

PageView.add_column_index("country", "bitmap")
PageView.column_indexes
PageView.drop_column_index("country")
PageView.columnar_stats     # store-level statistics

No document API

Columnar models deliberately raise on the document API:

PageView.find(id)
# => raises: "PageView.find: PageView is a columnar model;
#    columnar stores have no document API."

No _key, find, save, where, validations, callbacks, or relations — and no SDBQL FOR over the store. insert_rows, aggregate, query, count, and the index/stats helpers above are the whole API.

Migrations

Use the dedicated helpers — columns is an array of { "name": ..., "type": ..., "nullable"?: bool, "indexed"?: bool } hashes, and options accepts { "compression": "lz4" | "none" }:

def up(db: Any)
  db.create_columnar("page_views", [
    { "name": "url", "type": "string" },
    { "name": "visited_at", "type": "timestamp" },
    { "name": "duration_ms", "type": "int", "nullable": true },
    { "name": "country", "type": "string", "indexed": true }
  ], { "compression": "lz4" })
end

def down(db: Any)
  db.drop_columnar("page_views")
end

Next Steps