> Every UQL docs page, as Markdown: https://uql-orm.dev/llms.txt
> The same docs over MCP: https://uql-orm.dev/mcp
> Before writing UQL code, read the skill: https://uql-orm.dev/.well-known/agent-skills/uql-orm/SKILL.md

# JSON / JSONB

> Filter, update and sort by paths inside JSON columns, type-checked, on every SQL engine and MongoDB.

Source: https://uql-orm.dev/querying/json

UQL filters, sorts and updates by paths inside a JSON column, and type-checks those paths against the payload you declare, a `Json<T>` type or a schema’s output. It works on every SQL engine and on MongoDB.

## Declaring a JSON field

Wrap the field’s type in `Json<T>`:

```ts
import { f, Entity, Field, type Json } from 'uql-orm';

@Entity()
export class Company {
  @Field(f.number().autoIncrement())
  id?: number;

  @Field(f.jsonb().nullable())
  settings?: Json<{
    region?: string;
    isArchived?: boolean;
    seats?: number;
    tags?: string[];
    billing?: { plan?: string; coupon?: string };
  }> | null;
}
```

`Json<T>` is required. Without it, UQL reads a plain object type like `{ seats?: number }` as a relation, and `$where`, `$sort` and the update operators reject it. A column with no declared payload is a bare `Json`, which takes any value and any path. For a list of documents, use `Json<T>[]`; its paths address the elements, so `'lines.sku'` matches any line with that SKU.

On PostgreSQL, use `f.jsonb()` rather than `f.json()`: it can be indexed, and `$size`, `$all` and `$elemMatch` need it.

## A schema for the payload

Pass a [Standard Schema](https://standardschema.dev) to the builder, and the property’s type is that schema’s output. Zod, Valibot and ArkType all implement it (UQL doesn’t depend on them):

```ts
import { z } from 'zod';
import { f, Entity, Field, type Json } from 'uql-orm';

const Settings = z.object({
  region: z.string().optional(),
  seats: z.number().optional(),
});

@Entity()
export class Company {
  @Field(f.number().autoIncrement())
  id?: number;

  @Field(f.jsonb(Settings).nullable())
  settings?: Json<z.infer<typeof Settings>> | null;
}
```

UQL reads the schema only for its type. It does not validate a payload on write or read, so validate input in your own code before it reaches the ORM.

## Filtering

A dot-path in `$where` takes every [comparison operator](https://uql-orm.dev/querying/comparison-operators.md) its type allows:

```ts title="You write"
const companies = await pool.findMany(Company, {
  $where: {
    'settings.isArchived': { $ne: true },
    'settings.region': 'eu',
  },
});
```

PostgreSQL:

```sql
SELECT * FROM "Company"
WHERE ("settings"->'isArchived') <> $1::jsonb
  AND ("settings"->>'region') = $2
-- values: ['true', 'eu']
```

MySQL:

```sql
SELECT * FROM `Company`
WHERE `settings`->'$.isArchived' <> CAST(? AS JSON)
  AND (`settings`->>'$.region') = ?
-- values: ['true', 'eu']
```

MariaDB:

```sql
SELECT * FROM `Company`
WHERE JSON_EXTRACT(`settings`, '$.isArchived') <> JSON_EXTRACT(?, '$')
  AND JSON_VALUE(`settings`, '$.region') = ?
-- values: ['true', 'eu']
```

SQLite:

```sql
SELECT * FROM `Company`
WHERE (`settings` -> '$.isArchived') <> JSON(?)
  AND JSON_EXTRACT(`settings`, '$.region') = ?
-- values: ['true', 'eu']
```

SQL Server:

```sql
SELECT * FROM "Company"
WHERE (SELECT "value" FROM OPENJSON("settings", '$') WHERE "key" = N'isArchived') <> @p1
  AND (SELECT "value" FROM OPENJSON("settings", '$') WHERE "key" = N'region') = @p2
-- values: ['true', 'eu']
```

A typo in the path, an operator the type does not allow (`$size` on a string) or a value of the wrong type is a compile error. A bare `Json` accepts any path made of identifier keys (letters, digits and `_`, not starting with a digit), and throws at run time on any other key.

Numbers compare by value, so a stored `1.0` matches `1`, and a number comparison can use an index on the path. Booleans compare as JSON and strings as text. On PostgreSQL, a number comparison matches only where the path holds a number. `$not` and `$between` work on paths as they do on columns.

> **A missing key is NULL**
>
> On SQL, a missing key reads as `NULL`, so `{ $ne: true }` leaves out a row with no `isArchived` key. See [how `NULL` compares](https://uql-orm.dev/querying/comparison-operators.md#null-is-compared-as-the-engine-compares-it).

For arrays, `$size`, `$all` and `$elemMatch` work on paths too. An object or array in `$all`, or an object in `$elemMatch`, matches an element that contains everything it lists, whatever else the element holds; a key given an operator map inside it is compared as a path is. A path holding a scalar or an object instead of an array matches none of them on SQL. On MongoDB, `$all` also matches a scalar equal to its one value, and on MySQL and MariaDB, `$all: [3]` also matches `[[3]]`.

## Sorting

`$sort` takes the same paths, and numbers sort by value on every engine:

```ts
const companies = await pool.findMany(Company, {
  $sort: { 'settings.seats': 'desc' },
});
```

## Updating

Five operators change part of the document in one atomic statement, leaving the rest alone. Starting from `{ region: 'eu', seats: 12, tags: ['legacy', 'stale'] }`:

```ts
await pool.updateOneById(Company, id, {
  settings: { $set: { region: 'us' } }, // region: 'us'
});
await pool.updateOneById(Company, id, {
  settings: { $unset: ['seats'] }, // seats removed
});
await pool.updateOneById(Company, id, {
  settings: { $push: { tags: 'new' } }, // tags: ['legacy', 'stale', 'new']
});
await pool.updateOneById(Company, id, {
  settings: { $pull: { tags: 'stale' } }, // tags: ['legacy']
});
await pool.updateOneById(Company, id, {
  settings: { $addToSet: { tags: 'legacy' } }, // tags: ['legacy'], already there
});
```

Here is the `$set` above on each engine:

PostgreSQL:

```sql
UPDATE "Company" SET "settings" = JSONB_SET(COALESCE("settings", '{}'::jsonb), '{"region"}', $1::jsonb) WHERE "id" = $2
-- values: ['"us"', id]
```

MySQL:

```sql
UPDATE `Company` SET `settings` = JSON_SET(COALESCE(`settings`, JSON_OBJECT()), '$.region', CAST(? AS JSON)) WHERE `id` = ?
-- values: ['"us"', id]
```

MariaDB:

```sql
UPDATE `Company` SET `settings` = JSON_SET(COALESCE(`settings`, JSON_OBJECT()), '$.region', JSON_EXTRACT(?, '$')) WHERE `id` = ?
-- values: ['"us"', id]
```

SQLite:

```sql
UPDATE `Company` SET `settings` = JSON_SET(COALESCE(`settings`, JSON_OBJECT()), '$.region', JSON(?)) WHERE `id` = ?
-- values: ['"us"', id]
```

SQL Server:

```sql
UPDATE "Company" SET "settings" = JSON_MODIFY(COALESCE("settings", '{}'), '$.region', @p1) WHERE "id" = @p2
-- values: ['us', id]
```

`$set`, `$push` and `$addToSet` on a `NULL` column write into an empty document. `$push` and `$addToSet` make a one-element array of a missing or `null` key; `$addToSet` appends a string, finite number or boolean only where the array lacks it. `$pull` removes every element equal to the value, `null` included, and leaves an empty array when it removes the last one. All three leave a value that is no array as it is, and `$pull` a missing key. For object elements, key order matters to `$pull` on SQLite, SQL Server and MongoDB, but not on PostgreSQL, MySQL or MariaDB.

`$set` replaces each key you name, arrays included, so use `$push`, `$pull` and `$addToSet` to change an array’s elements. A dotted path writes one nested value and keeps its siblings, and `$unset` takes one too:

```ts
await pool.updateOneById(Company, id, {
  settings: { $set: { 'billing.plan': 'pro' }, $unset: ['billing.coupon'] },
});
```

Every parent the path passes through is created where it is missing, `NULL` or not an object, on every engine, a `NULL` column included. A path never steps into an array.

One update can combine operators, on one key too. They apply in order, `$pull`, `$set`, `$push`, `$addToSet`, `$unset`, each seeing what the one before wrote: `$pull` and `$push` on one key replace an element, and `$set` of `billing` with `billing.plan` merges. MongoDB refuses two operators on one path, so UQL works the result out before the statement runs, the same on every engine. Only a value set by `sql` takes no other operator inside it.

The operators are checked against `T`: `$push` and `$pull` accept only array keys and their element type, `$addToSet` only arrays of scalars, and a dotted path only a path of `T` with that path’s type. They work in update payloads (`updateOneById`, `updateMany`, and an upsert’s [`$update`](https://uql-orm.dev/querying/methods.md#updating-a-conflicting-row-its-own-way)). Inserts and the rows `upsertOne`, `upsertMany` and `saveOne` write take plain values only.

A JSON array column (`Json<T[]>` or `Json<T>[]`) takes `$pull`, `$push` and `$addToSet`, each with a list of elements, in that order: every element equal to one pulled goes, each pushed one is appended, and each added one is appended only if the array lacks it. Elements compare by type as well as value, so `1`, `'1'` and `true` are three. A `NULL` column counts as an empty array, and a stored value that is no array stays as it is:

```ts
import { f } from 'uql-orm';
@Entity()
export class Article {
  @Field(f.number().autoIncrement())
  id?: number;

  @Field(f.jsonb().nullable())
  tags?: Json<string[]> | null;
}

// tags: ['news', 'draft']
await pool.updateOneById(Article, id, {
  tags: { $pull: ['draft'], $addToSet: ['news', 'release'] },
}); // tags: ['news', 'release']
```

## Engine support

What each operation compiles to:

| Operation | PostgreSQL | MySQL | MariaDB | SQLite | SQL Server |
| - | - | - | - | - | - |
| Filter | `->>'key'` | `->>'key'` | `JSON_VALUE()` | `JSON_EXTRACT()` | `OPENJSON()` |
| Sort | `->'key'` | `->'key'` | `CAST(JSON_VALUE())`, then text | `JSON_EXTRACT()` | `TRY_CAST(OPENJSON())`, then text |
| `$set` | `JSONB_SET()` | `JSON_SET()` | `JSON_SET()` | `JSON_SET()` | `JSON_MODIFY()` |
| `$unset` | `#-` | `JSON_REMOVE()` | `JSON_REMOVE()` | `JSON_REMOVE()` | `JSON_MODIFY(..., NULL)` |
| `$push` | `JSONB_SET()` + `\|\|` | `JSON_ARRAY_APPEND()` | `JSON_ARRAY_APPEND()` | `JSON_INSERT()` | `JSON_MODIFY('append $')` |
| `$pull` | `JSONB_AGG()` filter | `JSON_TABLE()` filter | `JSON_TABLE()` + `JSON_EQUALS()` | `JSON_EACH()` filter | `OPENJSON()` + `STRING_AGG()` |
| `$addToSet` | `@>` + `JSONB_SET()` | `JSON_CONTAINS()` | `JSON_CONTAINS()` | `JSON_EACH()` | `OPENJSON()` + `JSON_MODIFY()` |
| `$size` | `JSONB_ARRAY_LENGTH()` | `JSON_LENGTH()` | `JSON_LENGTH()` | `JSON_ARRAY_LENGTH()` | `OPENJSON()` + `COUNT(*)` |
| `$all` | `@> ::jsonb` | `JSON_CONTAINS()` | `JSON_CONTAINS()` | `JSON_EACH()` | `OPENJSON()` |
| `$elemMatch` | `JSONB_ARRAY_ELEMENTS` | `JSON_TABLE()`¹ | `JSON_TABLE()` | `JSON_EACH()` | `OPENJSON()` |

¹ A single `$eq` or `$in` on an element uses containment instead (`JSON_OVERLAPS()` for several), which an index can serve.

CockroachDB and every Postgres pool emit the PostgreSQL SQL, except for binding: `BunSqlQuerierPool` writes `( $N::text )::jsonb` where Bun’s client needs it, the others `$N::jsonb`. MongoDB stores JSON natively and takes the same operators by the same rules.

Minimum versions are PostgreSQL 16, MySQL 8.4, MariaDB 12.2, SQLite 3.45 and SQL Server 2017. `$pull` alone has older floors: MySQL 8.0.4 (`JSON_TABLE`), MariaDB 10.7 (`JSON_EQUALS`) and SQLite 3.38 (`->`).

To index a filtered path, read it off the column’s ref, `index([company.settings.region])`: see [JSON indexes](https://uql-orm.dev/entities/indexes.md#json-indexes). For an array searched by `$all` or a scalar `$elemMatch`, index its elements with `{ column: company.settings.tags, type: [String] }`.

## Next steps

- [Comparison Operators](https://uql-orm.dev/querying/comparison-operators.md): the operators a path takes.
- [Decorators](https://uql-orm.dev/entities/basic.md): declaring `json` and `jsonb` fields.
- [Full-Text Search](https://uql-orm.dev/querying/full-text.md): searching text columns instead.