ES|QL `MV_INTERSECTS` function

Checks if any value from one multi-value exists in another.

Syntax

Embedded

Parameters

field1: Expression that can be null, a single value, or multiple values.
field2: Expression that can be null, a single value, or multiple values.

Description

Checks if any value yielded by the second multivalue expression is present in the values yielded by the first multivalue expression. Returns a boolean. Null values are treated as an empty set.

Supported types

field1	field2	result
boolean	boolean	boolean
cartesian_point	cartesian_point	boolean
cartesian_shape	cartesian_shape	boolean
date	date	boolean
date_nanos	date_nanos	boolean
double	double	boolean
flattened The Elastic Stack includes Elastic's core products such as Elasticsearch, Kibana, Logstash, and Beats. Availability Preview since 9.5 This functionality is in technical preview and is ready for evaluation. Use with caution in production; it is not recommended for mission-critical workloads. Elastic will work to fix any issues, but features in technical preview are not subject to the support SLA of official GA features. Specific Support terms apply. Unless stated otherwise on the page, this functionality is available when your Elastic Stack is deployed on Elastic Cloud Hosted, Elastic Cloud Enterprise, Elastic Cloud on Kubernetes, and self-managed environments. This documentation corresponds to the latest patch available for each minor version. If you're not using the latest patch, check the release notes for changes.	flattened The Elastic Stack includes Elastic's core products such as Elasticsearch, Kibana, Logstash, and Beats. Availability Preview since 9.5 This functionality is in technical preview and is ready for evaluation. Use with caution in production; it is not recommended for mission-critical workloads. Elastic will work to fix any issues, but features in technical preview are not subject to the support SLA of official GA features. Specific Support terms apply. Unless stated otherwise on the page, this functionality is available when your Elastic Stack is deployed on Elastic Cloud Hosted, Elastic Cloud Enterprise, Elastic Cloud on Kubernetes, and self-managed environments. This documentation corresponds to the latest patch available for each minor version. If you're not using the latest patch, check the release notes for changes.	boolean
geo_point	geo_point	boolean
geo_shape	geo_shape	boolean
geohash	geohash	boolean
geohex	geohex	boolean
geotile	geotile	boolean
integer	integer	boolean
ip	ip	boolean
keyword	keyword	boolean
keyword	text	boolean
long	long	boolean
text	keyword	boolean
text	text	boolean
unsigned_long	unsigned_long	boolean
version	version	boolean

Examples

ROW set = ["a", "b", "c"], element = "a"
| EVAL set_intersects_element = mv_intersects(set, element)

set:keyword	element:keyword	set_intersects_element:boolean
[a, b, c]	a	true

ROW setA = ["a","c"], setB = ["a", "b", "c"]
| EVAL a_intersects_b = mv_intersects(setB, setA)
| EVAL b_intersects_a = mv_intersects(setA, setB)

setA:keyword	setB:keyword	a_intersects_b:boolean	b_intersects_a:boolean
[a, c]	[a, b, c]	true	true

FROM airports
| WHERE mv_intersects(type, ["major"]) AND scalerank == 9
| KEEP scalerank, name, country
| SORT country

scalerank:integer	name:text	country:keyword
9	Chandigarh Int'l	India
9	Cheongju Int'l	South Korea