AI in the WHERE Clause: AI_FILTER and AI_FILTER_AGG

SELECT * FROM reviews WHERE AI_FILTER('is a complaint about shipping', body).

If that line of SQL works, a whole class of "I just need to find the rows where X holds, and X isn't a regex" problems disappears. Gnok's AI_FILTER family lands the natural-language predicate in the place where you'd actually write it — alongside =, LIKE, and BETWEEN.

This post walks through the four new functions, the patterns they unlock, and how to keep cost predictable when LLMs are sitting in your hot path.

Before you start

These functions call Gnok's AI provider. AI is off for an organization until an organization administrator, in Studio under Operations → AI Governance, saves a monthly token budget as Available and enables a rollout policy (provider, model, allowed operations, and egress and retention approvals). See AI Scalar Functions.

The four new functions

Function	Shape	Purpose
AI_FILTER(predicate, text)	scalar → BOOLEAN	per-row predicate; one LLM call per distinct text
AI_FILTER_AGG(predicate, text_col)	aggregate → BOOLEAN	per-group predicate over concatenated rows; one LLM call per group
AI_CLASSIFY(text, labels)	scalar → VARCHAR	alias for AI_CLASSIFY_TEXT (common spelling)
AI_CLASSIFY_AGG(text_col, labels)	aggregate → VARCHAR	one classification per group

These join the existing AI_* family (AI_COMPLETE, AI_SUMMARIZE, AI_SUMMARIZE_AGG, AI_TRANSLATE, AI_EXTRACT_ANSWER, AI_AGG). The unifying idea: the LLM is just a function. SQL already knows how to push, parallelize, and de-duplicate function calls — let it.

The argument order is prompt first, text second — the prompt is a constant baked into the system prompt, the text is the per-row column.

AI_FILTER as a row predicate

The classic use case is finding rows that match a fuzzy condition no regex catches:

-- "Show me complaints about shipping"
SELECT review_id, body, rating
FROM   reviews
WHERE  AI_FILTER('is a complaint about shipping or delivery', body)
  AND  rating <= 3
ORDER  BY created_at DESC
LIMIT  20;

Two things to notice:

1. The model only sees rows that pass the cheap filter first. The query planner keeps rating <= 3 predicates ahead of the LLM call, so you only pay for the small slice that matters.
2. De-duplication. Under the hood, AI_FILTER rewrites to LOWER(AI_COMPLETE(<prompt> + body)) = 'yes'. AI_COMPLETE de-dupes identical inputs across a batch — if the same body shows up twice, the model is asked once, so the number of model calls is typically less than the row count.

AI_FILTER_AGG — one call per group

Some predicates are about the group, not the row. "Show me products where reviews mention battery life":

SELECT product_id
FROM   reviews
GROUP  BY product_id
HAVING AI_FILTER_AGG('reviews mention battery life', body)
  AND  COUNT(*) >= 5;

The aggregate concatenates the rows in each group (delimited by the AISQL separator), runs AI_FILTER once over the combined text, and returns the group-level boolean. One LLM call per product instead of one per review.

AI_CLASSIFY_AGG — group-level labels

Same pattern, classification flavor:

SELECT
    product_id,
    AI_CLASSIFY_AGG(body, ARRAY['quality_issue', 'shipping_issue', 'usability_issue', 'other']) AS top_complaint
FROM    reviews
WHERE   rating <= 2
GROUP   BY product_id;

You get one label per product. The model sees up to N rows of review text per group (clipped at the engine's per-call token budget; longer groups truncate from the end with a warning).

Patterns that work

Cheap filters first. Always pin AI_FILTER behind a selective non-AI predicate. This isn't a query-planner hint; it's a cost reality. The cheaper your pre-filter, the less you pay.

Materialize for stability. AI calls are non-deterministic. If you need consistent answers across dashboard refreshes, write the filter to a materialized view:

CREATE MATERIALIZED VIEW shipping_complaints AS
SELECT review_id, body, rating
FROM   reviews
WHERE  rating <= 3
  AND  AI_FILTER('is a complaint about shipping or delivery', body);

REFRESH MATERIALIZED VIEW shipping_complaints;

Stable IDs flow into your downstream joins and the cost is paid once per refresh, not once per dashboard view.

LIMIT before AI_FILTER when you can. If the question is "show me 20 examples of X", an ORDER BY ts DESC LIMIT 1000 ahead of the AI predicate caps your worst-case spend at 1,000 LLM calls. The planner can't always do this for you because it doesn't know AI_FILTER is expensive — be explicit.

Keeping an eye on usage

AI_* calls draw on your organization's monthly AI token budget. Studio shows the budget's used, reserved, and remaining tokens under Operations → AI Governance. Because identical inputs within a batch are sent to the model once, a query usually makes fewer model calls than it has rows.

Bind-time validation

The functions are special-cased in the binder so syntax errors fire fast:

• AI_FILTER / AI_FILTER_AGG / AI_AGG require exactly two args: (prompt_literal, text).
• AI_CLASSIFY_AGG requires (text_column, labels[]).
• The prompt / labels arg must be a string literal — it gets baked into the system prompt and isn't allowed to vary per row. Per-row prompts would defeat de-duplication and produce nonsensical audit logs.

If you try AI_FILTER(predicate_col, body), you get a clear "AI_FILTER: first argument must be a literal prompt" error at bind time, not a mysterious runtime miss-classification. Empty literals are caught with a separate "non-empty string literal" error.

Why this matters

Most "natural language → SQL" stories put the LLM in the parser: take a question, generate a query, execute it. That's a different feature (ASK does it). The AI_FILTER family puts the LLM in the execution path as a cell-by-cell function. You write the SQL; the LLM evaluates predicates the engine can't express.

Both have their place. AI_FILTER is the one you reach for when:

• Your data is already structured (you know the table, the columns, the filter logic).
• The thing the regex / LIKE / classifier can't catch is a semantic predicate.
• You want the result to compose with JOIN, GROUP BY, WINDOW, materialized views — every other SQL feature.

Drop one into a WHERE clause against a table you already know and see what happens.