AI agent skill · by @lightdash

Filter Expressions

Author filter expressions for run_metric_query and search_field_values, including conditional custom metric filters.

Archived 2026-10-23 · Source updated 2026-09-18. Check the original repository for the latest version.

SKILL.md instructions

<!-- Generated by pnpm -F backend generate:filter-expression-skill. -->

Read this before writing filters for expression-enabled tools. It does not apply to tools whose schemas use structured filter objects. Follow each tool's input schema; this skill does not enable unavailable tools or filter modes.

### Filter Expressions

For `run_metric_query`, set `queryConfig.filters` to null when no query filters are needed. Otherwise, `dimensions`, `metrics`, and `tableCalculations` are independent string expressions or null.

- A non-null category contains one raw string expression.
- Choose the category from discovered field metadata (its dimension/metric kind), never from a field name or whether a numeric field sounds metric-like. A raw numeric dimension belongs in `dimensions`; only metrics and custom metrics belong in `metrics`.
- A field used only to restrict rows stays in its filter expression. Do not add it to `queryConfig.dimensions` unless the user asked to group by or display it: extra selected dimensions change aggregation and table-calculation grain.
- Table calculations belong only in `tableCalculations`.
- Each category is flat and uses AND or OR, never both.
- The three categories combine implicitly with AND.
- Aggregation custom metric filters are flat AND expressions.
- Omit `search_field_values.filters` for an unscoped search. When present, it is one flat dimension expression string and is AND-only.
- The tool schema is authoritative for where each string is placed.

Raw placement examples:

- dimensions: orders_status equals=completed,shipped AND orders_order_date inThePast=2{unit:weeks,completed:true}
- dimensions (alternatives): orders_promo_code startsWith=VIP OR orders_promo_code endsWith=25
- metrics: orders_total_order_amount greaterThan=100
- tableCalculations: rank lessThanOrEqual=10

- Join flat rules with AND or OR. Do not mix both connectors in one expression.
- Bare scalars work. Double-quote reserved and whitespace/punctuation strings (apostrophes/parentheses); quote when unsure. Quoted commas/braces literal; backslash escapes. E.g. `orders_product_name equals="Coffee Filters (100pk)"`.
- Limits: 256 rules; 256 values including settings/rule; 256 characters/literal; 16384 characters/expression.

### string

Grammar: <field> <operator form>

- `isNull [0 values]`
- `notNull [0 values]`
- `equals=<value>[,<value>...] [1+ values]`
- `notEquals=<value>[,<value>...] [1+ values]`
- `startsWith=<value>[,<value>...] [1+ values]`
- `endsWith=<value>[,<value>...] [1+ values]`
- `include=<value>[,<value>...] [1+ values]`
- `doesNotInclude=<value>[,<value>...] [1+ values]`

### number

Grammar: <field> <operator form>

- `isNull [0 values]`
- `notNull [0 values]`
- `equals=<value>[,<value>...] [1+ values]`
- `notEquals=<value>[,<value>...] [1+ values]`
- `lessThan=<value> [1 value]`
- `lessThanOrEqual=<value> [1 value]`
- `greaterThan=<value> [1 value]`
- `greaterThanOrEqual=<value> [1 value]`
- `inBetween=<first value>,<second value> [2 values]`
- `notInBetween=<first value>,<second value> [2 values]`

### date

Grammar: <field> <operator form>

- `isNull [0 values]`
- `notNull [0 values]`
- `equals=<value>[,<value>...] [1+ values]`
- `notEquals=<value>[,<value>...] [1+ values]`
- `lessThan=<value> [1 value]`
- `lessThanOrEqual=<value> [1 value]`
- `greaterThan=<value> [1 value]`
- `greaterThanOrEqual=<value> [1 value]`
- `inThePast=<count>{unit:<unit>,completed:<bool>} [1 count; settings required]`
- `notInThePast=<count>{unit:<unit>,completed:<bool>} [1 count; settings required]`
- `inTheNext=<count>{unit:<unit>,completed:<bool>} [1 count; settings required]`
- `inTheCurrent=<unit> [1 unit]`
- `notInTheCurrent=<unit> [1 unit]`
- `inBetween=<first value>,<second value> [2 values]`
- Units: days, weeks, months, quarters, years; completed=false includes partial, true completed only.

### boolean

Grammar: <field> <operator form>

- `isNull [0 values]`
- `notNull [0 values]`
- `equals=<value> [1 value]`
- `notEquals=<value> [1 value]`