JSON / JSONB

UQL provides first-class support for JSON/JSONB fields across PostgreSQL, MySQL, MariaDB, and SQLite: query, update, and sort by nested JSON properties with the same type-safe API on every dialect.

PostgreSQL examples default to Node.js + pg

Unless stated otherwise, PostgreSQL snippets on this page match what PgQuerierPool / PgDialect (the node-postgres driver) produce:

• JSON payloads for jsonb are stringified in the ORM and bound as parameters, then cast with $N::jsonb in SQL (no ( $N::text )::jsonb hop).
• Array parameters for operators like $in / $nin (= ANY / <> ALL) are passed as native JavaScript arrays; pg encodes them on the wire.

If you use BunSqlQuerierPool against Postgres, UQL switches to BunSqlPostgresDialect: JSON fragments often use ( $N::text )::jsonb, and arrays are sent as Postgres array literal strings instead. See Bun Native SQL.

Entity Setup

Wrap JSONB field types with Json<T> to enable full type safety: IDE autocompletion for dot-notation paths, $set keys, $unset keys, and $push/$pull targets.

import { Entity, Id, Field, type Json } from 'uql-orm';

@Entity()
export class Company {
  @Id({ type: Number })
  id?: number;

  @Field({ type: String })
  name?: string;

  @Field({ type: 'jsonb' })
  settings?: Json<{
    theme?: string;
    locale?: string;
    isArchived?: boolean;
    seats?: number;
    tags?: string[];
  }>;
}

Why Json<T>?

Without Json<T>, plain object types like { seats?: number } are classified as RelationKey instead of FieldKey, breaking usage in $where, $select, $sort, and $set. The Json<T> marker type solves this cleanly.

---

Starting Data

Every example on this page runs against this single row, so you can follow how its JSON document changes step by step.

You write

import { pool } from './uql.config.js';
import { Company } from './shared/models/index.js';

const id = await pool.insertOne(Company, {
  name: 'Acme',
  settings: { theme: 'dark', locale: 'en', isArchived: false, seats: 12, tags: ['legacy', 'stale-tag'] },
});

PostgreSQL, node-pg

INSERT INTO "Company" ("name", "settings") VALUES ($1, $2::jsonb) RETURNING "id" "id"
-- values: ['Acme', '{"theme":"dark","locale":"en","isArchived":false,"seats":12,"tags":["legacy","stale-tag"]}']

MySQL

INSERT INTO `Company` (`name`, `settings`) VALUES (?, ?)

MariaDB

INSERT INTO `Company` (`name`, `settings`) VALUES (?, ?) RETURNING `id` `id`

SQLite

INSERT INTO `Company` (`name`, `settings`) VALUES (?, ?) RETURNING `id` `id`

The document is stringified in the ORM and bound as a single parameter, so an insert writes the whole value at once. Inserts and upserts take plain values only: the operators below belong to update payloads.

---

Filtering (Dot-Notation)

Query nested JSON properties using dot-notation paths in $where. Every comparison operator applicable to the path’s value type is supported.

Dot-notation keys are fully typed: paths are restricted to real JSON fields, and each path resolves its value type from Json<T>, so a typo’d path ('settings.thme'), a dot-path on a non-JSON field, an operator that does not apply to the path’s type ($size on a string-typed path), or a mismatched value (a number where the path is a string) is a compile error. An untyped Json<unknown> field keeps its flexibility: any field.suffix path is accepted with permissive values.

You write

const companies = await pool.findMany(Company, {
  $where: {
    'settings.isArchived': { $ne: true },
    'settings.theme': 'dark',
  },
});
// -> matches the row above: isArchived is false and theme is 'dark'

PostgreSQL, node-pg

SELECT * FROM "Company"
WHERE ("settings"->'isArchived') IS DISTINCT FROM $1::jsonb
  AND ("settings"->>'theme') = $2
-- values: ['true', 'dark']

MySQL

SELECT * FROM `Company`
WHERE NOT (`settings`->'$.isArchived' <=> CAST(? AS JSON))
  AND (`settings`->>'$.theme') = ?

MariaDB

SELECT * FROM `Company`
WHERE NOT (JSON_EXTRACT(`settings`, '$.isArchived') <=> JSON_EXTRACT(?, '$'))
  AND JSON_VALUE(`settings`, '$.theme') = ?

SQLite

SELECT * FROM `Company`
WHERE (`settings`->'isArchived') IS NOT json(?)
  AND json_extract(`settings`, '$.theme') = ?

Null-Safe $ne

JSON-path $ne is null-safe on every dialect, so absent keys (SQL NULL) are correctly included in results: PostgreSQL uses IS DISTINCT FROM, SQLite uses IS NOT, and MySQL/MariaDB negate the null-safe <=>.

MariaDB JSON Path Access

MariaDB does not support MySQL’s -> / ->> JSON shorthand operators. UQL uses JSON_VALUE() for MariaDB dot-notation filtering and sorting, and JSON_EXTRACT() where the comparison happens on JSON values (booleans, objects, arrays), keeping SQL fully native and compatible.

---

Updating ($set / $unset / $push / $pull)

Atomically merge or remove keys in JSON fields directly from update payloads. No need to overwrite the entire JSON value.

Each example below starts from the row inserted above, and its trailing comment shows the resulting settings document.

$set: Assign Keys

Assign top-level keys of an existing JSON field. Keys not named are preserved.

You write

await pool.updateOneById(Company, id, {
  settings: { $set: { theme: 'light' } }, // -> theme: 'light', every other key untouched
});

PostgreSQL, node-pg

UPDATE "Company" SET "settings" = COALESCE("settings", '{}'::jsonb) || $1::jsonb WHERE "id" = $2
-- values: ['{"theme":"light"}', id]

MySQL

UPDATE `Company` SET `settings` = JSON_SET(COALESCE(`settings`, '{}'), '$.theme', CAST(? AS JSON)) WHERE `id` = ?
-- values: ['"light"', id]

MariaDB

UPDATE `Company` SET `settings` = JSON_SET(COALESCE(`settings`, '{}'), '$.theme', JSON_EXTRACT(?, '$')) WHERE `id` = ?
-- values: ['"light"', id]

SQLite

UPDATE `Company` SET `settings` = json_set(COALESCE(`settings`, '{}'), '$.theme', json(?)) WHERE `id` = ?
-- values: ['"light"', id]

$unset: Remove Keys

Remove specific keys from a JSON field.

You write

await pool.updateOneById(Company, id, {
  settings: { $unset: ['locale'] }, // -> the locale key is gone
});

PostgreSQL, node-pg

UPDATE "Company" SET "settings" = ("settings") - $1::text[] WHERE "id" = $2
-- values: [['locale'], id]

MySQL

UPDATE `Company` SET `settings` = JSON_REMOVE(`settings`, '$.locale') WHERE `id` = ?

MariaDB

UPDATE `Company` SET `settings` = JSON_REMOVE(`settings`, '$.locale') WHERE `id` = ?

SQLite

UPDATE `Company` SET `settings` = json_remove(`settings`, '$.locale') WHERE `id` = ?

$push: Append to Array

Append a value to the end of a JSON array. Only keys whose type is an array are valid $push targets (type-checked at compile time). If the key does not exist yet, it is created with a single-element array - identically on every dialect.

You write

await pool.updateOneById(Company, id, {
  settings: { $push: { tags: 'new-tag' } }, // -> tags: ['legacy', 'stale-tag', 'new-tag']
});

PostgreSQL, node-pg

UPDATE "Company" SET "settings" = jsonb_set("settings", '{tags}', COALESCE(("settings")->'tags', '[]'::jsonb) || jsonb_build_array($1::jsonb)) WHERE "id" = $2
-- values: ['"new-tag"', id]

MySQL

UPDATE `Company` SET `settings` = JSON_MERGE_PRESERVE(`settings`, JSON_OBJECT('tags', JSON_ARRAY(CAST(? AS JSON)))) WHERE `id` = ?
-- values: ['"new-tag"', id]

MariaDB

UPDATE `Company` SET `settings` = JSON_MERGE_PRESERVE(`settings`, JSON_OBJECT('tags', JSON_ARRAY(JSON_EXTRACT(?, '$')))) WHERE `id` = ?
-- values: ['"new-tag"', id]

SQLite

UPDATE `Company` SET `settings` = json_insert(`settings`, '$.tags[#]', json(?)) WHERE `id` = ?
-- values: ['"new-tag"', id]

$pull: Remove From Array

Remove every element equal to the given value. Like $push, only array-typed keys are valid targets and the value is typed as the array’s element.

You write

await pool.updateOneById(Company, id, {
  settings: { $pull: { tags: 'stale-tag' } }, // -> tags: ['legacy']
});

PostgreSQL, node-pg

UPDATE "Company" SET "settings" = jsonb_set("settings", '{tags}', COALESCE((
  SELECT jsonb_agg(_uql_pull.val ORDER BY _uql_pull.ord)
  FROM jsonb_array_elements("settings"->'tags') WITH ORDINALITY AS _uql_pull(val, ord)
  WHERE _uql_pull.val <> $1::jsonb), '[]'::jsonb), false) WHERE "id" = $2
-- values: ['"stale-tag"', id]

MySQL

UPDATE `Company` SET `settings` = JSON_REPLACE(`settings`, '$.tags', (
  SELECT COALESCE(JSON_ARRAYAGG(_uql_pull.v), JSON_ARRAY())
  FROM JSON_TABLE(`settings`, '$.tags[*]' COLUMNS (v JSON PATH '$')) _uql_pull
  WHERE _uql_pull.v <> CAST(? AS JSON))) WHERE `id` = ?
-- values: ['"stale-tag"', id]

MariaDB

UPDATE `Company` SET `settings` = JSON_REPLACE(`settings`, '$.tags', (
  SELECT COALESCE(JSON_ARRAYAGG(JSON_COMPACT(_uql_pull.v)), JSON_ARRAY())
  FROM JSON_TABLE(`settings`, '$.tags[*]' COLUMNS (v JSON PATH '$')) _uql_pull
  WHERE NOT JSON_EQUALS(_uql_pull.v, JSON_EXTRACT(?, '$')))) WHERE `id` = ?
-- values: ['"stale-tag"', id]

SQLite

UPDATE `Company` SET `settings` = json_replace(`settings`, '$.tags', (
  SELECT json_group_array(json(`settings` -> _uql_pull.fullkey))
  FROM json_each(`settings`, '$.tags') _uql_pull
  WHERE `settings` -> _uql_pull.fullkey <> json(?))) WHERE `id` = ?
-- values: ['"stale-tag"', id]

A $pull on a key that does not exist (or on a NULL column) is a no-op: it never creates the key and never nulls the document. Removing the last element leaves an empty array, not a missing key.

Object elements and SQLite

PostgreSQL, MySQL, MariaDB and MongoDB compare elements semantically, so an object element matches regardless of key order. SQLite compares the element’s canonical JSON text, so an object element only matches when its key order matches what is stored. Scalar elements (strings, numbers, booleans) are exact everywhere.

Combining Operators

All four operators can be freely combined in a single, atomic update. They are applied in a fixed order - $pull -> $set -> $push -> $unset - so every combination produces the same result on every dialect.

You write

await pool.updateOneById(Company, id, {
  settings: { $set: { theme: 'light' }, $push: { tags: 'new-tag' }, $unset: ['locale'] },
  // -> { theme: 'light', isArchived: false, seats: 12, tags: ['legacy', 'stale-tag', 'new-tag'] }
});

That order is what makes “replace an element” a single atomic statement: the $pull filters the stored array and the $push appends to that result.

Atomically replace a tag

await pool.updateOneById(Company, id, {
  settings: { $pull: { tags: 'stale-tag' }, $push: { tags: 'fresh-tag' } }, // -> tags: ['legacy', 'fresh-tag']
});

Combining two operators on the same key works as well, and follows the same order: a $set replaces the array outright, so a $push beside it appends to the value you just set.

Set then append, on one key

await pool.updateOneById(Company, id, {
  settings: { $set: { tags: ['kept'] }, $push: { tags: 'appended' } }, // -> tags: ['kept', 'appended']
});

$set is shallow

$set overwrites the top-level keys you name: arrays are replaced entirely, not appended to. To append elements to a JSON array, use $push; to remove them, $pull.

Scope: update only

These operators belong to update payloads (updateOneById, updateMany, …). upsertOne/upsertMany/saveOne take a full entity, so they accept plain values only - passing an operator object there is a compile error on every dialect.

Type Safety

$set keys, $unset keys, and $push/$pull targets are all validated against the JSON field’s inner type T (from Json<T>). The IDE autocompletes valid keys and rejects invalid ones at compile time. $push/$pull additionally restrict targets to array-typed keys and expect the array’s element type as the value.

A field whose payload is itself an array (Json<T[]>) accepts no operators at all: they address object keys of the document, so none of them has a meaningful result on such a column. Assign the whole value instead. An untyped Json<unknown> payload stays permissive for all four.

How values are compared

Extracting a value from a JSON document yields text, which loses the value’s type, so UQL compares each JSON scalar in the representation every engine agrees on: numbers numerically (so a stored 1.0 still matches 1), booleans as JSON, and strings as text. You will see this in the generated SQL as a numeric cast or a JSON-valued accessor:

Generated SQL (MySQL)

CAST((`settings`->>'$.seats') AS DECIMAL) > ?    -- MySQL, numeric operand
`settings`->'$.isArchived' = CAST(? AS JSON)     -- MySQL, boolean operand
(`settings`->>'$.theme') = ?                     -- MySQL, string operand

---

Sorting (Dot-Notation)

Sort by nested JSON field values using the same dot-notation syntax.

You write

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

PostgreSQL, node-pg

SELECT * FROM "Company" ORDER BY ("settings"->>'seats') DESC

MySQL

SELECT * FROM `Company` ORDER BY (`settings`->>'$.seats') DESC

MariaDB

SELECT * FROM `Company` ORDER BY JSON_VALUE(`settings`, '$.seats') DESC

SQLite

SELECT * FROM `Company` ORDER BY json_extract(`settings`, '$.seats') DESC

---

Supported Dialects

All JSON features work across four SQL dialects:

Feature	PostgreSQL	MySQL	MariaDB	SQLite
Dot-notation filtering	->>'key'	->>'key'	JSON_VALUE()	json_extract()
$set	|| ::jsonb	JSON_SET()	JSON_SET()	json_set()
$unset	- ::text[]	JSON_REMOVE()	JSON_REMOVE()	json_remove()
$push	jsonb_set() + ||	JSON_MERGE_PRESERVE()	JSON_MERGE_PRESERVE()	json_insert()
$pull	jsonb_agg() filter	JSON_TABLE() filter	JSON_TABLE() + JSON_EQUALS()	json_each() filter
Dot-notation sorting	->>'key'	->>'key'	JSON_VALUE()	json_extract()
$size	jsonb_array_length()	JSON_LENGTH()	JSON_LENGTH()	json_array_length()
$all	@> ::jsonb	JSON_CONTAINS()	JSON_CONTAINS()	json_each()
$elemMatch	jsonb_array_elements	JSON_TABLE()	JSON_TABLE()	json_each()

Dialect Compatibility

This page targets modern, actively maintained database lines. Baselines below reflect the current compatibility target for generated SQL:

Dialect	Practical baseline	Notes
PostgreSQL	16+	Uses jsonb operators/functions (->>, `
MySQL	8.4+	Uses ->>, JSON_SET, JSON_REMOVE, JSON_MERGE_PRESERVE, JSON_TABLE ($pull needs 8.0.4+)
MariaDB	12.2+	Uses JSON_VALUE for dot-notation path extraction (not ->>), plus JSON_SET, JSON_REMOVE, JSON_MERGE_PRESERVE, JSON_TABLE ($pull needs 10.7+ for JSON_EQUALS)
SQLite	3.45+	Uses json_extract, json_set, json_remove, json_insert(..., '$[#]', ...) for append, and json_each/-> for $pull (needs 3.38+)

PostgreSQL: Use jsonb over json

For PostgreSQL, always prefer type: 'jsonb' over type: 'json'. JSONB is binary-stored, indexable, and faster for queries. Array operators ($size, $all, $elemMatch) use JSONB-specific functions (jsonb_array_length, @>, jsonb_array_elements) which require JSONB columns.

Same AST, different Postgres pools

PostgresDialect (shared SQL shape) stringifies JSON for jsonb binds. PgDialect (default with PgQuerierPool) uses $N::jsonb and native JS arrays for ANY/ALL. BunSqlPostgresDialect (with BunSqlQuerierPool) uses ( $N::text )::jsonb where Bun’s client needs it, and array-literal encoding for ANY/ALL. Neon and Cockroach pools use other PostgresDialect subclasses; JSON casts match the base $N::jsonb pattern unless you override capabilities.

Note

MongoDB stores JSON natively, and the update operators map straight onto its own: $set becomes dotted-path assignments, and $unset/$push/$pull map one-to-one. Because MongoDB rejects two operators targeting one path in a single update document, any payload where a path appears in more than one operator group is emitted as a single aggregation-pipeline update instead - composed in the same $pull -> $set -> $push -> $unset order, keeping the result atomic and identical to the SQL dialects.

---

Next Steps

• Comparison Operators: The operator set dot-notation paths draw from.
• Decorators: Declaring json / jsonb fields and other column types.
• Full-Text Search: Searching text columns instead of JSON paths.
• Querier API: The read and write methods these payloads go to.