Function buildLikeClause

apps/sim/lib/table/sql.ts:506–537

* Builds a case-insensitive pattern match against a JSONB cell using ILIKE. * `position` controls wildcard placement: `contains` → `%value%`, `startsWith` * → `value%`, `endsWith` → `%value`. When `negate` is set the match is inverted * and null cells are included — "does not contain X" should ke

(
  tableName: string,
  field: string,
  value: string,
  position: 'contains' | 'startsWith' | 'endsWith',
  options?: { negate?: boolean }
)

 * back to a sequential scan bounded by the `table_id` btree prefix.
 */
function buildLikeClause(
  tableName: string,
  field: string,
  value: string,
  position: 'contains' | 'startsWith' | 'endsWith',
  options?: { negate?: boolean }
): SQL {
  const escapedField = field.replace(/'/g, "''")
  // Coerce defensively: filters arriving via the raw v1 API / tools may carry a
  // non-string value (e.g. `{ $contains: 123 }`), and ILIKE compares text anyway.
  const text = String(value)
  // An empty pattern collapses to `%`/`%%`, which matches every non-null row —
  // a silent footgun for raw-API callers (the UI gates empty values out). Reject
  // it, consistent with the range/`$empty` operand validation.
  if (text.length === 0) {
    const opName = position === 'contains' && options?.negate ? 'ncontains' : position
    throw new TableQueryValidationError(
      `$${opName} on column "${field}" requires a non-empty value`
    )
  }
  const escaped = escapeLikePattern(text)
  const pattern =
    position === 'startsWith'
      ? `${escaped}%`
      : position === 'endsWith'
        ? `%${escaped}`
        : `%${escaped}%`
  const cell = sql.raw(`${tableName}.data->>'${escapedField}'`)
  return options?.negate
    ? sql`(${cell} IS NULL OR ${cell} NOT ILIKE ${pattern})`
    : sql`${cell} ILIKE ${pattern}`
}

/**
 * Coerces a `$empty` operand to a boolean. Accepts a real boolean (the UI path)