Bewerken

Delen via


has_any operator

Applies to: ✅ Microsoft FabricAzure Data ExplorerAzure MonitorMicrosoft 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
... ...