has_any operator
Applies to: ✅ Microsoft Fabric ✅ Azure Data Explorer ✅ Azure Monitor ✅ Microsoft Sentinel
Filters a record set for data with any set of case-insensitive strings. has_any 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.
Performance tips
Note
When more than 128 search terms are used, text index lookup optimization is disabled, which might lead to reduced query performance.
Syntax
T | where col has_any (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 10,000 distinct values. |
Note
An inline tabular expression must be enclosed with double parentheses. See example.
Returns
Rows in T for which the predicate is true.
Examples
List of scalars
The following query shows how to use has_any with a comma-separated list of scalar values.
StormEvents
| where State has_any ("CAROLINA", "DAKOTA", "NEW")
| summarize count() by State
Output
| State | count_ |
|---|---|
| NEW YORK | 1750 |
| NORTH CAROLINA | 1721 |
| SOUTH DAKOTA | 1567 |
| NEW JERSEY | 1044 |
| SOUTH CAROLINA | 915 |
| NORTH DAKOTA | 905 |
| NEW MEXICO | 527 |
| NEW HAMPSHIRE | 394 |
Dynamic array
The following query shows how to use has_any with a dynamic array.
StormEvents
| where State has_any (dynamic(['south', 'north']))
| summarize count() by State
Output
| State | count_ |
|---|---|
| NORTH CAROLINA | 1721 |
| SOUTH DAKOTA | 1567 |
| SOUTH CAROLINA | 915 |
| NORTH DAKOTA | 905 |
| ATLANTIC SOUTH | 193 |
| ATLANTIC NORTH | 188 |
The same query can also be written with a let statement.
let areas = dynamic(['south', 'north']);
StormEvents
| where State has_any (areas)
| summarize count() by State
Output
| State | count_ |
|---|---|
| NORTH CAROLINA | 1721 |
| SOUTH DAKOTA | 1567 |
| SOUTH CAROLINA | 915 |
| NORTH DAKOTA | 905 |
| ATLANTIC SOUTH | 193 |
| ATLANTIC NORTH | 188 |
Tabular expression
The following query shows how to use has_any with an inline tabular expression. Notice that an inline tabular expression must be enclosed with double parentheses.
StormEvents
| where State has_any ((PopulationData | where Population > 5000000 | project State))
| summarize count() by State
Output
| State | count_ |
|---|---|
| TEXAS | 4701 |
| ILLINOIS | 2022 |
| MISSOURI | 2016 |
| GEORGIA | 1983 |
| MINNESOTA | 1881 |
| ... | ... |
The same query can also be written with a let statement. Notice that the double parentheses as provided in the last example aren't necessary in this case.
let large_states = PopulationData | where Population > 5000000 | project State;
StormEvents
| where State has_any (large_states)
| summarize count() by State
Output
| State | count_ |
|---|---|
| TEXAS | 4701 |
| ILLINOIS | 2022 |
| MISSOURI | 2016 |
| GEORGIA | 1983 |
| MINNESOTA | 1881 |
| ... | ... |