Pattern matching operators

Pattern matching operators filter ltree paths using PostgreSQL lquery and ltxtquery syntax. Pattern arguments are strings (or string[] for array matching). They are not separate column types.

import { db } from "../prisma/db";

matchesLquery()

Match a path against a single lquery pattern.

const rows = await db.orm.Category.where((c) => c.path.matchesLquery("*.computers.*"))
  .select("id", "path")
  .all();

Common lquery syntax:

• *: matches zero or more labels (default quantifier {,})
• *{1}: exactly one label; *{n} / *{n,m} / *{n,} / *{,m}: other quantifiers on wildcard labels
• {a,b,c}: matches any one of the labels at that position
• |: alternation within a label

SQL equivalent: path ~ $1::lquery

Examples

Paths exactly two labels deep:

await db.orm.Category.where((c) => c.path.matchesLquery("*{1}.*{1}"))
  .select("id", "path")
  .all();

Paths under electronics at any depth:

await db.orm.Category.where((c) => c.path.matchesLquery("electronics.*"))
  .select("id", "path")
  .all();

Direct children of electronics only:

await db.orm.Category.where((c) => c.path.matchesLquery("electronics.*{1}"))
  .select("id", "path")
  .all();

matchesLqueryArray()

Match if the path satisfies any pattern in a string[].

const rows = await db.orm.Category.where((c) =>
  c.path.matchesLqueryArray(["electronics.*", "software.*"]),
)
  .select("id", "path")
  .all();

SQL equivalent: path ? $1::lquery[]

matchesLtxtquery()

Match using ltxtquery full-text-style patterns over path labels (words combined with &, |, !).

const rows = await db.orm.Category.where((c) => c.path.matchesLtxtquery("computer | phone"))
  .select("id", "path")
  .all();

SQL equivalent: path @ $1::ltxtquery

Pattern syntax reference

Refer to the PostgreSQL ltree documentation for full pattern syntax.

Pattern operators use a Generalized Search Tree (GiST) index. See Add a GiST index.