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.

Basic Usage ​

typescript
// Column: metadata jsonb
// Data: { "user": { "id": 123, "role": "admin" } }

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

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 ("metadata" #>> '{score}') ~ '^-?[0-9]+(\.[0-9]+)?$'
//      THEN ("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 ("metadata" #>> '{score}') ~ '^-?[0-9]+(\.[0-9]+)?$'
//           THEN ("metadata" #>> '{score}')::numeric ELSE NULL END > 50)

Ordering ​

typescript
{ order: ['metadata.priority DESC'] }
// SQL: ORDER BY "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: