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.

Run the query

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.

Run the query

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.

Run the query

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