IGNIS

Appearance

JSON/JSONB Filtering ​

Query nested fields within JSON/JSONB columns using dot notation. PostgreSQL-specific.

FormatExampleSQL path
Simple fieldmetadata.name{name}
Nested fieldmetadata.user.email{user,email}
Array indexmetadata.tags[0]{tags,0}
Nested with arraymetadata.items[2].name{items,2,name}
Kebab-casemetadata.user-id{user-id}

NOTE

A key is recognized as a JSON path if it contains a . or [. The column named by the first path segment must be a json/jsonb column, or the query throws.

Typed JSON-path keys ​

TWhere<T> type-checks a JSON-path key against the columns of T - no cast, no TWhere<any>:

typescript
import type { TWhere } from '@venizia/ignis-filter';

type Product = { id: number; metadata: unknown };

const where: TWhere<Product> = { 'metadata.a.b': 'value' }; // compiles - metadata is a JSON column
CheckedNot checked
The column part of the key. A typo ('metdata.a') or a path on a non-JSON column ('score.a' when score is a number) is a compile error.The value on a JSON path - its type is unknown, so any value compiles. Runtime validation (Path Validation, Numeric Casting) still checks it at query time.

A column counts as a JSON column when its value can hold JSON: unknown, any, or an object other than Date or TIsoTimestamp.

NOTE

A relation-shaped object field counts as a JSON column at the type level - 'creator.name' type-checks when creator is a related entity's shape, not a real column. The runtime still throws Column 'creator' is not a JSON column, since a relation is never a JSON column at the database level.

Basic Usage ​

typescript
// Table: Product, column: metadata jsonb
// Data: { "user": { "id": 123, "role": "admin" } }

{ where: { 'metadata.user.role': 'admin' } }
// SQL: "Product"."metadata" #>> '{"user","role"}' = 'admin'

NOTE

The column renders through the same Drizzle column object a plain key uses, so it is qualified with the table (or the query alias, on an included relation) exactly like a plain key would be. Only the path ('{"user","role"}') is a raw literal.

All standard operators work with a JSON path key:

typescript
{ where: { 'metadata.score': { gt: 80 } } }
{ where: { 'metadata.level': { ilike: '%high%' } } }
{ where: { 'metadata.status': { in: ['pending', 'review'] } } }
{ where: { 'metadata.code': { regexp: '^[A-Z]+$' } } }

NOTE

If the path does not exist in a row's JSON, #>> returns NULL - the row is safely excluded, never an error.

Numeric Casting ​

A JSON #>> extraction is text, so a numeric comparison needs a cast or Postgres raises operator does not exist. IGNIS wraps the extraction in a safe CASE expression, decided per operator:

typescript
{ where: { 'metadata.score': { gt: 50 } } }
// SQL: CASE WHEN ("Product"."metadata" #>> '{"score"}') ~ '^-?[0-9]+(\.[0-9]+)?$'
//      THEN ("Product"."metadata" #>> '{"score"}')::numeric ELSE NULL END > 50
OperatorsCasts to numeric when...
gt, gte, lt, ltethe operand is a number
between, notBetweenboth bounds are numbers
eq, ne, neqthe operand is a number
in, inq, ninevery array element is a number
like, ilike, nlike, nilike, regexp, iregexpnever - always text
direct value (no operator object)the value is typeof number

NOTE

A numeric-looking string still passes the cast ("85" -> 85); a non-numeric string or null falls through to NULL and never matches.

The cast applies per operator, not once for the whole object - a mixed object casts only the operators that need it:

typescript
{ where: { 'metadata.score': { gte: 1, like: '%a%' } } }
// gte casts to numeric; like stays text - both read the same #>> extraction

not recurses into whatever it wraps, so a numeric operator nested under not still gets cast:

typescript
{ where: { 'metadata.score': { not: { gt: 50 } } } }
// SQL: NOT (CASE WHEN ("Product"."metadata" #>> '{"score"}') ~ '^-?[0-9]+(\.[0-9]+)?$'
//           THEN ("Product"."metadata" #>> '{"score"}')::numeric ELSE NULL END > 50)

Ordering ​

typescript
{ order: ['metadata.priority DESC'] }
// SQL: ORDER BY "Product"."metadata" #> '{"priority"}' DESC

NOTE

Ordering uses #> (returns JSONB, preserves native type ordering); where uses #>> (returns text) instead.

Path Validation ​

Every path component must match /^[a-zA-Z_][a-zA-Z0-9_-]*$|^\d+$/ - a letter/underscore start followed by letters, digits, underscore or hyphen, or a bare digit run for an array index.

typescript
'metadata.fieldName'        // valid
'data.meta-data'            // valid - kebab-case allowed
'data.123invalid'           // invalid - starts with a digit outside array-index context
'metadata.field;DROP TABLE' // invalid - throws

NOTE

A path on a non-JSON column also throws: Column 'name' is not a JSON column.

See also ​

Files: