has_all operator

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