IGNIS

Appearance

Pro Tips & Edge Cases ​

A filter can return the wrong rows even when every operator name looks right. Each entry below is verified against FilterBuilder/PostgresQueryOperators in packages/connectors.

NOT IN and != silently exclude NULL ​

This is SQL three-valued logic, not an IGNIS quirk. A row whose column is NULL never matches nin, ne, or neq, because NULL <> value evaluates to UNKNOWN rather than TRUE.

typescript
{ where: { status: { nin: ['deleted'] } } }
// Rows where status IS NULL are NOT returned

// To include them, add an explicit NULL branch:
{ where: { or: [{ status: { nin: ['deleted'] } }, { status: { is: null } }] } }

Empty arrays are not no-ops ​

typescript
{ where: { id: { in: [] } } }   // SQL: WHERE false  - matches nothing
{ where: { id: { nin: [] } } }  // SQL: WHERE true   - matches everything

This matters most when the array comes from user input - check its length before building the filter:

typescript
const ids = getUserSelectedIds();
if (ids.length === 0) {
  return []; // early return instead of an accidental WHERE false or WHERE true
}

JSON numeric comparisons need actual JSON numbers ​

A JSON path comparison casts safely with CASE WHEN (...) ~ '^-?[0-9]+(\.[0-9]+)?$' THEN (...)::numeric ELSE NULL END. The regex has to match, or the cast yields NULL and the row is excluded:

typescript
// { "priority": "3" } (string) - regex matches "3" -> casts to numeric 3 -> 3 > 2 matches
{ where: { 'metadata.priority': { gt: 2 } } }

// { "priority": "high" } - regex fails -> NULL -> never matches
{ where: { 'metadata.priority': { gt: 2 } } }

Store numbers as JSON numbers ({ "priority": 3 }), not numeric strings, to avoid depending on the cast.

A missing JSON path is a null-safe miss, not an error ​

typescript
// Table: Product, column: metadata jsonb
{ where: { 'metadata.nonexistent.field': 'value' } }
// SQL: "Product"."metadata" #>> '{"nonexistent","field"}' = 'value'
// No rows match (NULL != 'value') - no exception either

JSON order uses #>, not #>> ​

#> returns JSONB and preserves the value's type; the equality/comparison operators above use #>> (text) instead, because sorting needs JSONB's native ordering:

typescript
// Table: Product, column: metadata jsonb
{ order: ['metadata.priority DESC'] }
// SQL: "Product"."metadata" #> '{"priority"}' DESC

// JSONB comparison order: null < boolean < number < string < array < object

Array operators accept a bare value ​

contains, containedBy, and overlaps wrap a non-array operand in a single-element array automatically:

typescript
{ where: { tags: { contains: ['featured'] } } }
{ where: { tags: { contains: 'featured' } } }
// Equivalent

fields as an object only supports inclusion ​

typescript
{ fields: { id: true, name: true, email: true } }

// Setting a key to `false` does NOT exclude it - the key is ignored.
// To exclude fields, list only the ones you want, as an array:
{ fields: ['id', 'name', 'email'] }

Combining multiple array conditions ​

contains, containedBy, and overlaps compose like any other operators - as separate keys under an implicit AND:

typescript
await productRepository.find({
  filter: {
    where: {
      categories: { contains: ['electronics', 'portable'] },  // must have ALL these
      tags: { containedBy: ['new', 'sale', 'featured', 'popular'] }, // must be a subset
      suppliers: { overlaps: ['supplier-a', 'supplier-b'] },  // at least one match
    },
  },
});

Chunk very large in arrays ​

Postgres has no hard IN-list limit, but a multi-thousand-element array is worth chunking for query-plan and payload-size reasons.

Every chunk needs its own limit. find() fills in filter.limit ?? settings.defaultLimit ?? 10, so a chunk without one returns at most 10 rows and the loop quietly drops the rest:

typescript
const allIds = getLargeIdList(); // e.g. 5000 IDs
const chunkSize = 500;
const results = [];

for (let i = 0; i < allIds.length; i += chunkSize) {
  const chunk = allIds.slice(i, i + chunkSize);
  const rows = await repository.find({
    // Without `limit`, this chunk returns 10 rows, not 500.
    filter: { where: { id: { in: chunk } }, limit: chunkSize },
  });
  results.push(...rows);
}

WARNING

Keep chunkSize at or below the model's maxLimit. That ceiling defaults to 1000, and an explicit limit above it throws before the query runs. Raise it with @model({ settings: { maxLimit } }) when you genuinely need bigger chunks - see Fields, Ordering & Pagination.

Factor out reusable where/pagination fragments ​

A filter is a plain object, so common fragments compose with spreads instead of being rewritten per call site:

typescript
const createActiveFilter = <T extends { status: string; deletedAt: Date | null }>(): TWhere<T> =>
  ({ status: 'active', deletedAt: { is: null } }) as TWhere<T>;

const createPaginationFilter = (page: number, size = 20) => ({
  limit: size,
  skip: (page - 1) * size,
});

const products = await productRepository.find({
  filter: {
    where: { ...createActiveFilter(), category: 'electronics' },
    ...createPaginationFilter(3),
  },
});

See also ​

Files: