Skip to content
UQL
NewFrom Mongoose to SQL: migrating MongoDB to PostgreSQL7 min read

JSON / JSONB

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

Section titled “Declaring a JSON field”

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

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

Section titled “A schema for the payload”

Pass a Standard Schema 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):

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

Section titled “Filtering”

A dot-path in $where takes every comparison operator its type allows:

You write
const companies = await pool.findMany(Company, {
  $where: {
    'settings.isArchived': { $ne: true },
    'settings.region': 'eu',
  },
});
SELECT * FROM "Company"
WHERE ("settings"->'isArchived') <> $1::jsonb
  AND ("settings"->>'region') = $2
-- 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.

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

Section titled “Sorting”

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

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

Updating

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

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:

UPDATE "Company" SET "settings" = JSONB_SET(COALESCE("settings", '{}'::jsonb), '{"region"}', $1::jsonb) WHERE "id" = $2
-- 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:

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). 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:

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

Section titled “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. For an array searched by $all or a scalar $elemMatch, index its elements with { column: company.settings.tags, type: [String] }.

Next steps

Section titled “Next steps”
UQLMIT LicenseGitHubnpmRSSllms.txt