# JSON Columns

> Query and update JSON columns with dotted paths and SQLite JSON functions

Package: Kunii
Canonical: https://kuratchi.dev/docs/kunii/json-columns
Markdown: https://kuratchi.dev/docs/kunii/json-columns.md

## Schema

Declare a column as `type: 'json'` in the schema (or `json` in the DSL) and the ORM treats it as a first-class JSON document. Whole-object writes are stringified, reads are parsed back, and dotted keys against JSON columns compile to SQLite JSON SQL.

```ts
const schema = defineSchema({
  name: 'app',
  version: 1,
  tables: {
    events: {
      id: 'integer primary key',
      kind: 'text not null',
      payload: 'json',
    },
  },
});
```

## Reads

Dotted keys become `->>` expressions:

```ts
// payload is { user: { tier: 'pro' } }
await db.events.where({ 'payload.user.tier': 'pro' }).many();
// SELECT * FROM events WHERE payload ->> '$.user.tier' = ?

await db.events.where({ 'payload.score': { gte: 50 } }).many();
// SELECT * FROM events WHERE payload ->> '$.score' >= ?

await db.events.where({ 'payload.tier': { in: ['pro', 'enterprise'] } }).many();
// payload ->> '$.tier' IN (SELECT value FROM json_each(?))

await db.events.orderBy({ 'payload.priority': 'desc' }).many();
// ORDER BY payload ->> '$.priority' DESC

await db.events.select(['id', 'payload.user.email']).many();
// SELECT id, payload ->> '$.user.email' AS "payload.user.email" FROM events
```

Array indexing works inline:

```ts
db.events.where({ 'payload.tags[0]': 'urgent' });
// payload ->> '$.tags[0]' = ?
```

## Writes

Dotted keys become `json_set` calls:

```ts
await db.events.where({ id: 1 }).update({ 'payload.tier': 'pro' });
// UPDATE events SET payload = json_set(payload, '$.tier', ?) WHERE id = ?
```

Multiple paths on the same column merge into a single `json_set` call:

```ts
await db.events.where({ id: 1 }).update({
  'payload.tier': 'pro',
  'payload.seats': 5,
});
// UPDATE events SET payload = json_set(payload, '$.tier', ?, '$.seats', ?) WHERE id = ?
```

Object and array values are bound as JSON via `json(?)` so SQLite stores them as nested JSON, not literal strings:

```ts
await db.events.where({ id: 1 }).update({ 'payload.flags': { beta: true } });
// UPDATE events SET payload = json_set(payload, '$.flags', json(?)) WHERE id = ?
//   bound: '{"beta":true}'
```

Whole-object writes still work and replace the entire blob:

```ts
await db.events.where({ id: 1 }).update({ payload: { whole: 'replace' } });
// UPDATE events SET payload = ? WHERE id = ?
```

## Why this matters

Without surgical JSON SQL, filtering on `payload.tier` would require pulling every row's `payload`, parsing it in your Worker, then filtering in JavaScript. With first-class JSON support, SQLite does the work and only matching rows cross the wire.

## Read next

- [Querying](/docs/kunii/querying)
- [Type Generation](/docs/kunii/type-generation)