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 using a consistent, type-safe API. UQL generates dialect-specific SQL automatically.

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, Json } from 'uql-orm';

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

  @Field()
  name?: string;

  @Field({ type: 'jsonb' })
  kind?: Json<{ public?: number; private?: number; tags?: string[] }>;

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

Why Json<T>?

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

---

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 querier.findMany(Company, {
  $where: {
    'settings.isArchived': { $ne: true },
    'settings.theme': 'dark',
  },
});

Generated SQL (PostgreSQL, node-pg)

SELECT * FROM "Company"
WHERE ("settings"->>'isArchived') IS DISTINCT FROM $1
  AND "settings"->>'theme' = $2

Generated SQL (MySQL)

SELECT * FROM `Company`
WHERE (`settings`->>'isArchived') <> ?
  AND (`settings`->>'theme') = ?

Generated SQL (MariaDB)

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

Generated SQL (SQLite)

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

Null-Safe $ne

For PostgreSQL and SQLite, JSON-path $ne uses null-safe operators (IS DISTINCT FROM and IS NOT) so absent keys (SQL NULL) are correctly included in results.

MariaDB JSON Path Access

MariaDB does not support MySQL’s -> / ->> JSON shorthand operators. UQL uses JSON_VALUE() for MariaDB dot-notation filtering and sorting to keep 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.

$set: Assign Keys

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

Renamed in 0.20.0

This operator was called $merge before 0.20.0. Rename $merge to $set in your update payloads.

“Merge” named the wrong thing three times over. RFC 7396 merge patch, which JSON_MERGE_PATCH / json_patch implement, is recursive, replaces arrays wholesale, and gives null the meaning “delete this key” - this operator does none of the three. UQL does use a merge function, JSON_MERGE_PRESERVE, but for $push. And MongoDB’s own $merge is an aggregation output stage. This operator is a shallow assignment of the keys you name, which is exactly MongoDB’s update $set.

You write

await querier.updateOneById(Company, id, {
  kind: { $set: { public: 1 } },
});

Generated SQL (PostgreSQL, node-pg)

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

Generated SQL (MySQL)

UPDATE `Company` SET `kind` = JSON_SET(COALESCE(`kind`, '{}'), '$.public', CAST(? AS JSON)) WHERE `id` = ?
-- values: ['1', id]

Generated SQL (MariaDB)

UPDATE `Company` SET `kind` = JSON_SET(COALESCE(`kind`, '{}'), '$.public', CAST(? AS JSON)) WHERE `id` = ?
-- values: ['1', id]

Generated SQL (SQLite)

UPDATE `Company` SET `kind` = json_set(COALESCE(`kind`, '{}'), '$.public', json(?)) WHERE `id` = ?
-- values: ['1', id]

$unset: Remove Keys

Remove specific keys from a JSON field.

You write

await querier.updateOneById(Company, id, {
  kind: { $unset: ['private'] },
});

Generated SQL (PostgreSQL, node-pg)

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

Generated SQL (MySQL)

UPDATE `Company` SET `kind` = JSON_REMOVE(`kind`, '$.private') WHERE `id` = ?

Generated SQL (MariaDB)

UPDATE `Company` SET `kind` = JSON_REMOVE(`kind`, '$.private') WHERE `id` = ?

Generated SQL (SQLite)

UPDATE `Company` SET `kind` = json_remove(`kind`, '$.private') 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.

Fixed in 0.20.0

Appending to a key that did not exist used to behave differently per dialect: PostgreSQL and SQLite created the array, MySQL silently did nothing, and MariaDB set the whole column to NULL (JSON_ARRAY_APPEND returns NULL for a missing path). All four now create the array.

You write

await querier.updateOneById(Company, id, {
  kind: { $push: { tags: 'new-tag' } },
});

Generated SQL (PostgreSQL, node-pg)

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

Generated SQL (MySQL)

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

Generated SQL (MariaDB)

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

Generated SQL (SQLite)

UPDATE `Company` SET `kind` = json_insert(`kind`, '$.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 querier.updateOneById(Company, id, {
  kind: { $pull: { tags: 'stale-tag' } },
});

Generated SQL (PostgreSQL, node-pg)

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

Generated SQL (MySQL)

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

Generated SQL (MariaDB)

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

Generated SQL (SQLite)

UPDATE `Company` SET `kind` = json_replace(`kind`, '$.tags', (
  SELECT json_group_array(json(`kind` -> uql_pull.fullkey))
  FROM json_each(`kind`, '$.tags') uql_pull
  WHERE `kind` -> 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 querier.updateOneById(Company, id, {
  kind: { $set: { public: 1 }, $push: { tags: 'new-tag' }, $unset: ['private'] },
});

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 querier.updateOneById(Company, id, {
  kind: { $pull: { tags: 'old' }, $push: { tags: 'new' } },
});

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 querier.updateOneById(Company, id, {
  kind: { $set: { tags: ['kept'] }, $push: { tags: 'appended' } }, // -> ['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:

CAST((`kind`->>'$.public') AS DECIMAL) = ?   -- MySQL, numeric operand
kind->'$.isArchived' = CAST(? AS JSON)      -- MySQL, boolean operand
(`kind`->>'$.country') = ?                  -- MySQL, string operand

Before 0.20.0 everything compared as text, which failed outright on drivers that send typed parameters (operator does not exist: text = integer) and on MySQL made a boolean filter silently match nothing.

---

Sorting (Dot-Notation)

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

You write

const companies = await querier.findMany(Company, {
  $sort: { 'kind.public': 'desc' },
});

Generated SQL (PostgreSQL, node-pg)

SELECT * FROM "Company" ORDER BY "kind"->>'public' DESC

Generated SQL (MySQL)

SELECT * FROM `Company` ORDER BY (`kind`->>'public') DESC

Generated SQL (MariaDB)

SELECT * FROM `Company` ORDER BY JSON_VALUE(`kind`, '$.public') DESC

Generated SQL (SQLite)

SELECT * FROM `Company` ORDER BY json_extract(`kind`, '$.public') 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+)

About older versions

UQL may work on earlier versions for many JSON features, but this guide intentionally optimizes for the cleanest SQL and behavior on modern maintained releases.

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.

Before 0.20.0 these operators were not mapped on MongoDB at all - the operator object was written into the document as literal data.