Skip to content
UQL
NewComposite primary keys3 min read

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.

The PostgreSQL tabs below show what PgQuerierPool produces. The other Postgres pools differ only in how they spell a JSON parameter; see Dialect Compatibility.

Entity Setup

Section titled “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[];
  }>;
}

The Json<T> marker is what makes the column a field rather than a relation. Without it a plain object type like { seats?: number } is classified as a RelationKey, and $where, $select, $sort and $set stop accepting it.

A column holding a list of documents is declared Json<T>[], also a field rather than a to-many relation. Its dot-paths address the element:

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


  @Field({ type: 'jsonb' })
  lines?: Json<{ sku: string; qty: number }>[];
}


await pool.findMany(Order, {
  $where: { 'lines.sku': 'ACME-1' },
  $sort: { 'lines.qty': 'desc' },
});

Starting Data

Section titled “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'],
  },
});
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"]}']

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)

Section titled “Filtering (Dot-Notation)”

A dot-notation path in $where takes every comparison operator its value type allows. Each path resolves that type from Json<T>, so a typo’d path ('settings.thme'), a dot-path on a non-JSON field, an operator the type does not allow ($size on a string), or a mismatched value are all compile errors. An untyped Json<unknown> field stays permissive: any field.suffix path is accepted.

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

JSON-path $ne is null-safe on every dialect, so rows whose key is absent are included: PostgreSQL uses IS DISTINCT FROM, SQLite IS NOT, and MySQL and MariaDB negate the null-safe <=>.

MariaDB has no equivalent of MySQL’s -> and ->> shorthand, so UQL writes JSON_VALUE() there for dot-notation filtering and sorting, and JSON_EXTRACT() where the comparison is against a JSON value such as a boolean, object or array.

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

Section titled “Updating ($set / $unset / $push / $pull)”

Merge or remove keys atomically from an update payload, without rewriting the whole document. Each example starts from the row inserted above, and its trailing comment shows the resulting settings.

$set: Assign Keys

Section titled “$set: Assign Keys”

Assigns top-level keys; keys not named are preserved.

You write
await pool.updateOneById(Company, id, {
  settings: { $set: { theme: 'light' } }, // -> theme: 'light', every other key untouched
});
UPDATE "Company" SET "settings" = COALESCE("settings", '{}'::jsonb) || $1::jsonb WHERE "id" = $2
-- values: ['{"theme":"light"}', id]

$unset: Remove Keys

Section titled “$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
});
UPDATE "Company" SET "settings" = ("settings") - $1::text[] WHERE "id" = $2
-- values: [['locale'], id]

$push: Append to Array

Section titled “$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']
});
UPDATE "Company" SET "settings" = jsonb_set("settings", '{tags}', COALESCE(("settings")->'tags', '[]'::jsonb) || jsonb_build_array($1::jsonb)) WHERE "id" = $2
-- values: ['"new-tag"', id]

$pull: Remove From Array

Section titled “$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']
});
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]

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 are the one case where the engines differ. PostgreSQL, MySQL, MariaDB and MongoDB compare them semantically, so key order does not matter; SQLite compares the element’s canonical JSON text, so an object element matches only when its key order matches what is stored. Scalar elements are exact everywhere.

Combining Operators

Section titled “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']
});

All four operators belong to update payloads (updateOneById, updateMany, and so on). upsertOne, upsertMany and saveOne take a whole entity and accept plain values only, so passing an operator object there is a compile error.

Their keys are checked against the JSON field’s inner type T, so the editor completes valid keys and rejects the rest. $push and $pull narrow further to array-typed keys and expect the array’s element type. A column holding an array at the top level - written either Json<T[]> or Json<T>[] - accepts none of the four, since they all address object keys of one document: assign the whole value instead. An untyped Json<unknown> stays permissive.

Extracting a value from a JSON document yields text and loses its type, so UQL compares each scalar in the representation every engine agrees on: numbers numerically, so a stored 1.0 still matches 1; booleans as JSON; strings as text. That shows up in the SQL as a numeric cast or a JSON-valued accessor:

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

Sorting (Dot-Notation)

Section titled “Sorting (Dot-Notation)”

$sort takes the same paths:

You write
const companies = await pool.findMany(Company, {
  $sort: { 'settings.seats': 'desc' },
});
SELECT * FROM "Company" ORDER BY ("settings"->>'seats') DESC

Supported Dialects

Section titled “Supported 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

Section titled “Dialect Compatibility”

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

Dialect Practical baseline Version-specific caveats
PostgreSQL 16+ None; every operator above is available across supported lines.
MySQL 8.4+ $pull needs 8.0.4+, for JSON_TABLE.
MariaDB 12.2+ $pull needs 10.7+, for JSON_EQUALS.
SQLite 3.45+ $pull needs 3.38+, for the -> operator.

On PostgreSQL prefer type: 'jsonb' to type: 'json'. JSONB is stored binary and is indexable, and the array operators ($size, $all, $elemMatch) compile to jsonb_array_length, @> and jsonb_array_elements, none of which a json column supports.

A path these queries filter on can be indexed with jsonPath, and an array $all searches with jsonArray, MySQL’s multi-valued index. Both are declared as the query reads them, so the index and the filter compile to the same expression - which is the only reason the planner uses one.

Which Postgres dialect you get depends on the pool. PgDialect, the default under PgQuerierPool, writes $N::jsonb and passes native JavaScript arrays for ANY/ALL. BunSqlPostgresDialect, under BunSqlQuerierPool, writes ( $N::text )::jsonb where Bun’s client needs it and encodes those arrays as literals. The Neon and CockroachDB pools are other PostgresDialect subclasses and follow the base $N::jsonb pattern unless you override their capabilities.

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

Next Steps

Section titled “Next Steps”