ES|QL `MV_INTERSECTION` function

Returns values present in both multi-value fields.

Syntax

Embedded

Parameters

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

Description

Returns the values that appear in both input fields. Returns null if either field is null or if no values match.

Supported types

field1	field2	result
boolean	boolean	boolean
cartesian_point	cartesian_point	cartesian_point
cartesian_shape	cartesian_shape	cartesian_shape
date	date	date
date_nanos	date_nanos	date_nanos
double	double	double
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.	flattened
geo_point	geo_point	geo_point
geo_shape	geo_shape	geo_shape
geohash	geohash	geohash
geohex	geohex	geohex
geotile	geotile	geotile
integer	integer	integer
ip	ip	ip
keyword	keyword	keyword
keyword	text	keyword
long	long	long
text	keyword	keyword
text	text	keyword
unsigned_long	unsigned_long	unsigned_long
version	version	version

Examples

ROW a = [1, 2, 3, 4, 5], b = [2, 3, 4, 5, 6]
| EVAL finalValue = MV_INTERSECTION(a, b)
| KEEP finalValue

finalValue:integer
[2, 3, 4, 5]

ROW a = [1, 2, 3, 4, 5]::long, b = [2, 3, 4, 5, 6]::long
| EVAL finalValue = MV_INTERSECTION(a, b)
| KEEP finalValue

finalValue:long
[2, 3, 4, 5]

ROW a = [true, false, false, false], b = [false]
| EVAL finalValue = MV_INTERSECTION(a, b)
| KEEP finalValue

finalValue:boolean
[false]

ROW a = [5.2, 10.5, 1.12345, 2.6928], b = [10.5, 2.6928]
| EVAL finalValue = MV_INTERSECTION(a, b)
| KEEP finalValue

finalValue:double
[10.5, 2.6928]

ROW a = ["one", "two", "three", "four", "five"], b = ["one", "four"]
| EVAL finalValue = MV_INTERSECTION(a, b)
| KEEP finalValue

finalValue:keyword
["one", "four"]