Filters a table to the subset of rows that satisfy a predicate.
filteroperators are equivalent
| where Predicate
|T||string||✓||Tabular input whose records are to be filtered.|
|Predicate||string||✓||Expression that evaluates to a bool for each row in T.|
Rows in T for which Predicate is
All filtering functions return false when compared with null values. Use special null-aware functions to write queries that handle null values.
Use simple comparisons between column names and constants. ('Constant' means constant over the table - so
ago()are OK, and so are scalar values assigned using a
For example, prefer
where Timestamp >= ago(1d)to
where bin(Timestamp, 1d) == ago(1d).
Simplest terms first: If you have multiple clauses conjoined with
and, put first the clauses that involve just one column. So
Timestamp > ago(1d) and OpId == EventIdis better than the other way around.
Order comparisons by complexity
The following query returns storm records that report damaged property, are floods, and start and end in different places.
Notice that we put the comparison between two columns last, as the where operator can't use the index and forces a scan.
StormEvents | project DamageProperty, EventType, BeginLocation, EndLocation | where DamageProperty > 0 and EventType == "Flood" and BeginLocation != EndLocation
The following table only shows the top 10 results. To see the full output, run the query.
|5000||Flood||FAYETTE CITY LOWBER|
|5000||Flood||MORRISVILLE WEST WAYNESBURG|
|10000||Flood||COPELAND HARRIS GROVE|
|5000||Flood||GLENFORD MT PERRY|
|25000||Flood||EAST SENECA BUFFALO AIRPARK ARPT|
|10000||Flood||GOODHOPE WEST MILFORD|
Check if column contains string
The following query returns the rows in which the word "cow" appears in any column.
StormEvents | where * has "cow"