JSON Aggregates & Updates

Building on the JSON filter helpers added in the schema-extensions release (whereJsonPath / whereJsonContains / whereJsonLength / selectJsonExtract), this page covers two new groups:

1. Aggregates — fold rows into JSON arrays or objects with groupBy()
2. Updates — in-place edits to JSON columns via SQLite's json_set / json_patch / json_remove

All helpers compile down to plain SQLite json1 calls, so they round-trip cleanly against D1 with no extension setup.

Aggregates — json_group_array / json_group_object

qb.selectJsonGroupArray(expr, alias)

Wrap any column or expression in json_group_array(<expr>). Pair with groupBy() to roll non-aggregated columns into a JSON array per group.

// Tags per post:
const rows = await Post.query()
  .select(['posts.id'])
  .selectJsonGroupArray('tags.name', 'tag_names')
  .join('tags', 'tags.post_id = posts.id')
  .groupBy('posts.id')
  .get()
// → SELECT posts.id, json_group_array(tags.name) AS tag_names ...

You can nest other JSON helpers inside the expression — useful for aggregating deep paths:

Order.query()
  .selectJsonGroupArray("json_extract(line_items, '$.sku')", 'skus')
  .groupBy('customer_id')
  .get()

qb.selectJsonGroupObject(keyExpr, valueExpr, alias)

// Settings per user as a single JSON object:
UserSetting.query()
  .select(['user_id'])
  .selectJsonGroupObject('key', 'value', 'settings')
  .groupBy('user_id')
  .get()
// → SELECT user_id, json_group_object(key, value) AS settings ...

keyExpr and valueExpr are emitted verbatim. Wrap untrusted identifiers with safeIdent().

qb.orderByJsonPath(col, path, dir?)

const sorted = await User.query().orderByJsonPath('settings', '$.priority', 'desc').get()
// → ORDER BY json_extract(settings, '$.priority') DESC

The path is escaped (single quotes doubled) before inlining; col is emitted verbatim. Wrap untrusted column names with safeIdent().

In-Place Updates — json_set / json_patch / json_remove

Each method issues a single UPDATE that rewrites just the targeted slice of the JSON column. Returns the affected row count via D1's meta.changes.

qb.updateJsonSet(col, path, value)

Set the value at path without rewriting the whole document.

await User.query().whereEq('id', uid).updateJsonSet('settings', '$.role', 'admin')
// UPDATE users SET settings = json_set(settings, ?, ?) WHERE id = ?
//   bindings: ['$.role', 'admin', uid]

value may be any JSON-serializable value (string, number, boolean, object, array). SQLite's json_set will create intermediate paths as needed.

qb.updateJsonPatch(col, patch)

Merge a JSON patch into the column via SQLite's json_patch — RFC 7396 merge semantics: nulls in the patch delete keys, nested objects deep-merge, arrays are replaced wholesale.

await User.query().whereEq('id', uid)
  .updateJsonPatch('settings', { theme: 'dark', notifications: { email: true } })
// UPDATE users SET settings = json_patch(settings, ?) WHERE id = ?
//   bindings: ['{"theme":"dark","notifications":{"email":true}}', uid]

qb.updateJsonRemove(col, path | paths[])

Strip one or more paths from the JSON document.

// Single path
await Post.query().whereEq('id', pid).updateJsonRemove('metadata', '$.draft')

// Multiple paths in one statement
await Post.query().whereEq('id', pid)
  .updateJsonRemove('metadata', ['$.draft', '$.tmp', '$.legacy'])
// UPDATE posts SET metadata = json_remove(metadata, ?, ?, ?) WHERE id = ?

Returns 0 without issuing a query when paths is an empty array.

Patterns

Atomic counter inside a JSON column

await User.query().whereEq('id', uid)
  .updateJsonSet('stats', '$.visits', currentVisits + 1)

For genuine race-free atomics you still want a dedicated column with UPDATE ... SET visits = visits + 1. JSON updates rewrite the whole document, so they're cheap but not atomic vs. concurrent writers.

Strip secrets after migration

await User.query().whereNotNull('legacy_token')
  .updateJsonRemove('credentials', ['$.legacy_token', '$.api_v1_secret'])

Compute a "tags by user" map for a dashboard

const byUser = await Post.query()
  .select(['user_id'])
  .selectJsonGroupArray("json_extract(tags, '$')", 'tags')
  .groupBy('user_id')
  .get()

Safety notes

• col, keyExpr, valueExpr, and aggregate expressions are emitted verbatim. Wrap untrusted identifiers with safeIdent() from the package.
• JSON paths are escaped (single quotes doubled) before inlining. Paths themselves come from your application code, not user input — but treat them as you would any SQL fragment.
• updateJsonPatch serializes the patch with JSON.stringify. If the patch isn't a plain JSON-serializable object, encode it yourself and pass a string.