Skip to content
UQL
NewComposite primary keys6 min read

JSON / JSONB

Query, update and sort by nested JSON properties with one type-safe API on PostgreSQL, MySQL, MariaDB and SQLite (and MongoDB).

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). A missing key is created as a single-element array 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 combine in a single atomic update, applied in a fixed order ($pull, $set, $push, $unset), so every combination gives 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 (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_SET()
$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”

Minimum versions for the SQL on this page:

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 compile to the same expression as the filter, which is what lets the planner use them.

Every Postgres pool builds the same PostgresDialect; what differs is how its driver binds. Under PgQuerierPool, Neon, PGlite and CockroachDB a JSON value binds as $N::jsonb and arrays go native for ANY/ALL. BunSqlQuerierPool passes the wire driver’s capabilities instead, which write ( $N::text )::jsonb where Bun’s client needs it and encode those arrays as literals.

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, in the same order, so the result stays atomic and matches the SQL dialects.

Next Steps

Section titled “Next Steps”