has_all operator
Applies to: ✅ Microsoft Fabric ✅ Azure Data Explorer ✅ Azure Monitor ✅ Microsoft Sentinel
Filters a record set for data with one or more case-insensitive search strings. has_all searches for indexed terms, where an indexed term is three or more characters. If your term is fewer than three characters, the query scans the values in the column, which is slower than looking up the term in the term index.
For more information about other operators and to determine which operator is most appropriate for your query, see datatype string operators.
Syntax
T | where col has_all (expression, ... )
Learn more about syntax conventions.
Parameters
| Name | Type | Required | Description |
|---|---|---|---|
| T | string |
✔️ | The tabular input to filter. |
| col | string |
✔️ | The column by which to filter. |
| expression | scalar or tabular | ✔️ | An expression that specifies the values for which to search. Each expression can be a scalar value or a tabular expression that produces a set of values. If a tabular expression has multiple columns, the first column is used. The search will consider up to 256 distinct values. |
Returns
Rows in T for which the predicate is true.
Examples
Set of scalars
The following query shows how to use has_all with a comma-separated set of scalar values.
StormEvents
| where EpisodeNarrative has_all ("cold", "strong", "afternoon", "hail")
| summarize Count=count() by EventType
| top 3 by Count
Output
| EventType | Count |
|---|---|
| Thunderstorm Wind | 517 |
| Hail | 392 |
| Flash Flood | 24 |
Dynamic array
The same result can be achieved using a dynamic array notation.
StormEvents
| where EpisodeNarrative has_all (dynamic(["cold", "strong", "afternoon", "hail"]))
| summarize Count=count() by EventType
| top 3 by Count
Output
| EventType | Count |
|---|---|
| Thunderstorm Wind | 517 |
| Hail | 392 |
| Flash Flood | 24 |
The same query can also be written with a let statement.
let criteria = dynamic(["cold", "strong", "afternoon", "hail"]);
StormEvents
| where EpisodeNarrative has_all (criteria)
| summarize Count=count() by EventType
| top 3 by Count
| EventType | Count |
|---|---|
| Thunderstorm Wind | 517 |
| Hail | 392 |
| Flash Flood | 24 |