Query Operators

Pipe operators form the backbone of any KQL query. Each operator corresponds to a SQL construct — for example, where maps to a WHERE clause, summarize maps to GROUP BY, and take maps to LIMIT. Chained operators are composed as nested subqueries when required by the target dialect.

Legend: ✅ Supported · ⚠️ Approximated · ❌ Not Supported · 🔄 Rewritten

Operator	SQLite	MySQL	ClickHouse	PostgreSQL	DuckDB	Notes
where	✅	✅	✅	✅	✅	Chained where clauses are ANDed
project	✅	✅	✅	✅	✅	Mapped to SELECT
project-away	✅	✅	✅	✅	✅	Removes named columns from SELECT
project-keep	✅	✅	✅	✅	✅	Keeps only named columns
project-rename	✅	✅	✅	✅	✅	Column alias
project-reorder	✅	✅	✅	✅	✅	Reorders SELECT list
extend	✅	✅	✅	✅	✅	Adds computed columns
summarize	✅	✅	✅	✅	✅	Maps to GROUP BY
summarize (no by)	✅	✅	✅	✅	✅	Global aggregate, no GROUP BY
summarize (named by key)	✅	✅	✅	✅	✅	e.g. by Day = bin(T, 1d) → SELECT <expr> AS Day … GROUP BY Day
order by	✅	✅	✅	✅	✅
sort by	✅	✅	✅	✅	✅	Alias for order by
top N by	✅	✅	✅	✅	✅	Maps to ORDER BY + LIMIT
top-nested	⚠️	⚠️	⚠️	⚠️	⚠️	Hierarchical top-N via ROW_NUMBER(), up to 2 levels; 3+ levels and the with others= bucket are not supported
top-hitters	✅	✅	✅	✅	✅	Lowered to GROUP BY … ORDER BY <metric> DESC LIMIT N
take	✅	✅	✅	✅	✅	Maps to LIMIT
limit	✅	✅	✅	✅	✅	Alias for take
count	✅	✅	✅	✅	✅	Wraps as subquery + COUNT(*)
distinct	✅	✅	✅	✅	✅	Maps to SELECT DISTINCT
union	✅	✅	✅	✅	✅	UNION ALL
union withsource=	✅	✅	✅	✅	✅	Adds source column to each branch
join	✅	✅	✅	✅	✅	See Join Types
lookup	✅	✅	✅	✅	✅	Defaults to LEFT JOIN
as	✅	✅	✅	✅	✅	Table alias
let (expression)	✅	✅	✅	✅	✅	Inlined as expression
let (tabular query)	✅	✅	✅	✅	✅	Mapped to CTE
let (function)	⚠️	⚠️	⚠️	⚠️	⚠️	Inlined when invoked at the top level — let parser = (p){ … }; parser(…), the wrapped ASIM-parser shape — where its inner lets and query are visited in place. Called from inside an expression it is not substituted, and its parameters are never bound to the arguments
let (datatable)	✅	✅	✅	✅	✅	CREATE TEMP TABLE + INSERT
mv-expand	❌	❌	✅	🔄	🔄	ClickHouse ARRAY JOIN; PostgreSQL/DuckDB CROSS JOIN LATERAL unnest(); SQLite/MySQL unsupported
mv-apply	❌	❌	✅	🔄	🔄	Same per-dialect handling as mv-expand, with the per-element sub-pipeline applied
parse	❌	❌	⚠️	❌	⚠️	ClickHouse extractAll, DuckDB regexp_extract_all; SQLite/MySQL/PostgreSQL have no extract-all function. The named columns the pattern declares are not produced — the SELECT is replaced by a single unaliased array of every capture, so results have to be indexed positionally
parse-where	❌	❌	⚠️	❌	⚠️	Like parse plus a row filter, and it loses the column names the same way
parse-kv	❌	❌	✅	❌	✅	ClickHouse extractKeyValuePairs; DuckDB per-key regexp_extract; others unsupported
fork	❌	❌	❌	❌	❌	Not implemented. One query cannot branch into several result sets in this transpiler
make-series	❌	❌	✅	✅	✅	Gap-filled series: ClickHouse -Resample combinator; PostgreSQL/DuckDB explicit grid + LEFT JOIN + array fill; SQLite/MySQL unsupported
sample	✅	✅	✅	✅	✅	ORDER BY rand()/random() LIMIT N
sample-distinct	✅	✅	✅	✅	✅	SELECT DISTINCT <col> … LIMIT N
partition	⚠️	⚠️	⚠️	⚠️	⚠️	Only the summarize and top sub-forms are lowered; a general per-partition subquery is unsupported
search	❌	❌	❌	❌	❌	No SQL equivalent — emitted as UNSUPPORTED on all dialects
range	❌	❌	✅	✅	✅	ClickHouse arrayJoin(range()); PostgreSQL generate_series(); DuckDB UNNEST(generate_series()); SQLite/MySQL unsupported
render	✅	✅	✅	✅	✅	No-op; chart type stored as hint only
serialize	✅	✅	✅	✅	✅	Treated as extend
print	✅	✅	✅	✅	✅	SELECT without FROM
consume	✅	✅	✅	✅	✅	Wraps as subquery + LIMIT 0
invoke	❌	❌	❌	❌	❌	No SQL equivalent for plugins
evaluate	❌	❌	❌	❌	❌	No SQL equivalent for plugins (except bag_unpack, below)
evaluate bag_unpack	❌	❌	⚠️	⚠️	⚠️	Keys inferred from static dynamic(...) literals; ClickHouse * EXCEPT, PostgreSQL/DuckDB * EXCLUDE; runtime-only bags, MySQL and SQLite unsupported
evaluate pivot	❌	❌	❌	❌	❌	No SQL equivalent
externaldata	❌	❌	❌	❌	❌	External file source — no SQL equivalent
getschema	❌	❌	❌	❌	❌	Not translated to SQL
scan	❌	❌	❌	❌	❌	Stateful row-sequence operator — no set-based SQL equivalent
nulls first/last	✅	✅	✅	✅	✅	Extra ORDER BY item injected
select (KQL alias)	✅	✅	✅	✅	✅	Mapped to project