JSON columns

json<T>() declares a jsonb column whose JSON shape is typed by T. The runtime decodes on read + validates on write — typos in your object literals fail the TypeScript check, not at runtime.

#Declaring a typed JSON column

import { table, id, json } from '@voltro/database'

interface NotePrefs {
  readonly fontSize: 'sm' | 'md' | 'lg'
  readonly collapsed: ReadonlyArray<string>
  readonly autoSave?: boolean
}

const notes = table('notes', {
  id:    id(),
  prefs: json<NotePrefs>().default({ fontSize: 'md', collapsed: [] }),
})

ctx.store.select('notes') returns prefs: NotePrefs — autocomplete works in your IDE, mutation inputs are checked too.

#Storage + validation per dialect

json() maps to each engine's native JSON type, and Voltro enforces JSON validity on write, on every dialect — invalid JSON is rejected by the database, not only by TypeScript:

Dialect	Column type	Validated on write
postgres	JSONB (binary, GIN-indexable)	yes (native)
mysql / mariadb	JSON	yes (mariadb's JSON is LONGTEXT + an auto json_valid CHECK)
mssql	NVARCHAR(MAX) + CHECK (ISJSON(col)=1)	yes
sqlite	TEXT + CHECK (json_valid(col))	yes

On postgres the binary JSONB form is what makes path queries + GIN indexing fast; the other engines store JSON as text (mariadb's information_schema reports the JSON column as longtext — that is what the JSON type is there). Either way, the jsonField(...) filters below and write-validation behave identically across all of them, and reads always come back as parsed objects/arrays — never raw strings.

On postgres, prefer jsonb over json (Voltro always emits jsonb): binary storage, GIN-indexable, faster. Plain json only preserves exact byte / whitespace / key-order — never what you want for app data.

#Path filters

Filter on a value inside a json() column with jsonField(column, ...path):

import { jsonField } from '@voltro/database'

database.users.where(jsonField('preferences', 'theme').eq('dark'))
database.events.where(jsonField('payload', 'amount').gt(1000))       // numeric, not lexical
database.docs.where(jsonField('meta', 'tags', 0).eq('urgent'))       // nested key + array index
database.users.where(jsonField('preferences', 'locale').inSet(['de', 'en']))
database.users.where(jsonField('preferences', 'theme').isNotNull())

Path segments are object keys (string) or array indices (number) → $.theme / $.tags[0].

Operators	Compare the extracted value as
eq neq inSet notInSet contains isNull isNotNull	text
gt gte lt lte	number (numeric ordering, not lexical)

The same jsonField(...) expression is portable across every backend — it lowers to each dialect's json accessor: postgres #>>, mysql/mariadb JSON_EXTRACT (+ JSON_UNQUOTE), mssql JSON_VALUE, sqlite / turso json_extract.

Reactive subscriptions filtered by a JSON path stay live, but the matcher treats the leaf as non-indexable: it's re-checked on every change to the table (the same way contains is). For a high-traffic filter, denormalise into a real indexed column — or index the specific path (see Indexing JSON columns below).

Scope. JSON-path filtering is a server-side query-DSL feature (query / mutation handlers). Clients can't yet send JSON-path filters over rpc — that's a deliberate later phase (it needs path allow-listing + validation). For arbitrary expressions the DSL doesn't model, drop to the store.raw escape hatch (see the query builder).

#Indexing JSON columns

#GIN index — broad coverage

A GIN index covers arbitrary containment queries on the whole jsonb document. Declare it with the table-level .index(name, [col], { kind: 'gin' }):

import { table, id, json } from '@voltro/database'

const events = table('events', {
  id:      id(),
  payload: json<unknown>(),
})
  .index('events_payload', ['payload'], { kind: 'gin' })

On postgres this emits CREATE INDEX "events_payload" ON "events" USING GIN ("payload"), so any containment query on payload is indexed.

GIN is postgres-only. On mysql / mariadb there is no GIN access method and a btree on a JSON column can't serve containment — the migrator skips the index and warns, pointing you at the single-path approach below. On mssql / sqlite it falls back to a plain btree + a warning. GIN is also large (often 30-50% of the table size for wide JSON); use the next option when you only filter on one specific path.

#Index a JSON path — jsonIndex

When 99% of your queries look like jsonField('payload', 'kind').eq('X'), index just that path with jsonIndex(column, ...path) inside .expressionIndex(...). It mirrors jsonField exactly — same column, same segments, lowered to the same per-dialect accessor — so a filter on that path can use the index:

import { table, id, json, jsonIndex } from '@voltro/database'

const events = table('events', { id: id(), payload: json<unknown>() })
  .expressionIndex('events_kind',   [jsonIndex('payload', 'kind')])
  .expressionIndex('events_amount', [jsonIndex('payload', 'amount').numeric()])

Text extraction (the default) backs eq / neq / inSet / contains; .numeric() backs the range ops (gt / gte / lt / lte). Because the index expression is byte-identical to the WHERE expression jsonField(...) compiles to, the optimiser actually picks it up.

Dialect support is uneven — this is a hard engine limit, not a Voltro choice. Only some engines can index an expression directly:

Dialect	jsonIndex	Notes
postgres	✓ expression index	((col #>> '{path}'::text[]))
mysql (8.0.13+)	✓ functional index	((JSON_UNQUOTE(JSON_EXTRACT(...))))
sqlite	✓ expression index	(json_extract(col, '$.path'))
mariadb	— skipped + warned	no expression-index support
mssql	— skipped + warned	no expression-index support

On mariadb / mssql the migrator skips a jsonIndex and warns rather than emit DDL the engine rejects. The idiomatic indexed-JSON path there is a generated / computed column (which Voltro already supports), then filter on that column instead of jsonField(...):

table('events', {
  id:      id(),
  payload: json<unknown>(),
  // materialise the path into a real, indexable column
  kind:    text().generatedAs(`JSON_UNQUOTE(JSON_EXTRACT(\`payload\`, '$.kind'))`),
}).index('events_kind', ['kind'])
// query: ctx.store.select('events').where('kind', 'click').all()

For an arbitrary expression the jsonIndex shorthand doesn't model, drop to the raw .expressionIndex(...) form with an { expr } entry:

table('events', { id: id(), payload: json<unknown>() })
  .expressionIndex('events_kind', [{ expr: `(payload->>'kind')` }])

There you own per-dialect correctness (the expr string is emitted verbatim; the postgres ->> form is shown).

#Writing JSON

ctx.store.insert('notes', {
  prefs: { fontSize: 'lg', collapsed: ['archive'] },
})

A plain update writes the WHOLE JSON value. To change one field without rewriting the rest, use patchJson — a server-side in-place JSON merge (no read-modify-write round-trip):

// Merge an object over the top level of the column:
await ctx.store.patchJson('notes', id, 'prefs', { autoSave: true })

// Set a nested path (dot-separated; the column is the first segment):
await ctx.store.patchJson('notes', id, 'prefs.theme', 'dark')

patchJson(table, pk, path, value) returns the post-image (or null when the row doesn't exist) and emits an update ChangeEvent so reactive subscribers see the change. The merge is server-side on every dialect — postgres jsonb_set / ||, mysql + mariadb JSON_SET, mssql JSON_MODIFY, sqlite json_set / json_patch.

When you need a full read-modify-write (e.g. computing the new value from the old in JS), read the row, change the object, and write it back inside the mutation's transaction:

const note = await ctx.store.select('notes').where('id', id).one()
await ctx.store.update('notes', id, {
  prefs: { ...note.prefs, autoSave: true },
})

#Validating JSON shape

Two layers of validation apply:

1. JSON validity — that the stored bytes are well-formed JSON — is enforced automatically by the database on every dialect (see Storage + validation per dialect). You don't declare anything.
2. JSON shape — that the value matches your expected structure — is up to you: enforce it at the table level with table().validate(Schema):

import { Schema } from 'effect'

table('notes', { id: id(), prefs: json<NotePrefs>() })
  .validate(Schema.Struct({
    prefs: Schema.Struct({ fontSize: Schema.Literal('sm', 'md', 'lg') }),
  }))

Decode failure throws a typed TableValidationFailed before the INSERT runs. There is no .check() modifier on a JSON column.

#When JSON is the wrong choice

JSON is great for:

• Free-form user-configurable data (preferences, layout configs)
• Sparse extensions (every row has different shape)
• Foreign-system payloads (Stripe webhook bodies, Slack message JSON)

JSON is a footgun for:

• Anything you filter on heavily. Denormalise into real columns — they're cheaper to query, easier to index, simpler to constrain.
• Anything with strict schema. A real column with a NOT NULL + CHECK is stronger than a JSON path constraint.
• Joining / relating to other tables. You can't FK from a JSON path.

Rule of thumb: if you'd write a migration to add a new field, it's a real column. If users add fields without your code changing, it's JSON.

#Anti-patterns

• json<any>() everywhere. Defeats the type-safety. Be specific.
• Putting a foreign key inside JSON. No FK constraint, no cascade, no clean join. Use a real reference(() => table) column.
• Storing big binary as JSON. Use the storage plugin (@voltro/plugin-storage) for blobs > a few KB.