ES|QL: Add MV_INTERSECTION Function #139379

markjhoy · 2025-12-11T14:30:00Z

Adds MV_INTERSECTION function to ES|QL.

Returns a subset of the inputs sets that contains the intersection of values in provided multi-valued arguments.

Example:

Given set A = {"a","b","c"} and set B = {"b","c","d"}
MV_INTERSECTION(A, B) returns {"b", "c"}

github-actions · 2025-12-11T21:15:07Z

🔍 Preview links for changed docs

github-actions · 2025-12-11T21:15:09Z

ℹ️ Important: Docs version tagging

👋 Thanks for updating the docs! Just a friendly reminder that our docs are now cumulative. This means all 9.x versions are documented on the same page and published off of the main branch, instead of creating separate pages for each minor version.

We use applies_to tags to mark version-specific features and changes.

Expand for a quick overview

When to use applies_to tags:

✅ At the page level to indicate which products/deployments the content applies to (mandatory)
✅ When features change state (e.g. preview, ga) in a specific version
✅ When availability differs across deployments and environments

What NOT to do:

❌ Don't remove or replace information that applies to an older version
❌ Don't add new information that applies to a specific version without an applies_to tag
❌ Don't forget that applies_to tags can be used at the page, section, and inline level

🤔 Need help?

Check out the cumulative docs guidelines
Reach out in the #docs Slack channel

elasticsearchmachine · 2025-12-12T14:25:32Z

Hi @markjhoy, I've created a changelog YAML for you.

elasticsearchmachine · 2025-12-12T14:25:33Z

Pinging @elastic/es-analytical-engine (Team:Analytics)

elasticsearchmachine · 2025-12-12T14:25:33Z

Pinging @elastic/search-eng (Team:SearchOrg)

markjhoy · 2025-12-12T14:31:55Z

.../java/org/elasticsearch/xpack/esql/expression/function/scalar/multivalue/MvIntersection.java

+            @Example(file = "mv_intersect", tag = "testMvIntersectWithBooleanValues"),
+            @Example(file = "mv_intersect", tag = "testMvIntersectWithDoubleValues"),
+            @Example(file = "mv_intersect", tag = "testMvIntersectWithBytesRefValues") },
+        appliesTo = { @FunctionAppliesTo(lifeCycle = FunctionAppliesToLifecycle.PREVIEW, version = "9.3.0") }


Just to doublecheck - are the tags here correct? (Preview for 9.3.0?)

kderusso

Changes LGTM, will defer to Ioana to approve after her feedback is met. Thanks!

kderusso · 2025-12-15T13:10:07Z

x-pack/plugin/esql/src/test/java/org/elasticsearch/xpack/esql/analysis/VerifierTests.java


    }

+    public void testMvIntersectValidatesDataTypesAreEqual() {


Any tests needed for null input, etc?

There's one at least in the IT tests ({null, null} -> {null}) as well as others that check for null as the first or second argument - so, I don't think it needs to be in the verifier... however the validation is going to change a bit from Ioana's suggestions...

craigtaverner · 2025-12-15T14:41:29Z

When comparing this with the geospatial functions, I would naturally think this function should be called MV_INTERSECTION, because the term MV_INTERSECT sounds too much like it should return a boolean.

Class	Operation	Spatial	MV/List/Array
Predicate	Intersects	ST_INTERSECTS	MV_INTERSECTS
Predicate	Contains	ST_CONTAINS	MV_CONTAINS
Set	Intersection	ST_INTERSECTION	MV_INTERSECTION

craigtaverner

I think we need to re-think the name. I propose MV_INTERSECTION both to better match set-theory as well as better symmetry with related GIS functions.

...ain/java/org/elasticsearch/xpack/esql/expression/function/scalar/multivalue/MvIntersect.java

...sql/src/main/java/org/elasticsearch/xpack/esql/expression/function/EsqlFunctionRegistry.java

ioanatia · 2025-12-15T15:13:43Z

When comparing this with the geospatial functions, I would naturally think this function should be called MV_INTERSECTION, because the term MV_INTERSECT sounds too much like it should return a boolean.

@craigtaverner that makes sense to change it to MV_INTERSECTION - we just took the name from #98545 and we assumed it was the right name

markjhoy · 2025-12-15T15:18:00Z

Good call @craigtaverner - I'll refactor, and I've also updated the meta issue as well to reflect this.

craigtaverner · 2025-12-15T17:23:21Z

Good call @craigtaverner - I'll refactor, and I've also updated the meta issue as well to reflect this.

Thanks! Great that you fixed the meta-issue too. Sometimes those issues are just a first guess as to what we need, so always good to second-guess! 😄

craigtaverner

Much nicer with the name change!

leemthompo

Thanks for iterating on the docs! One tiny little thing left to add: an inline applies_to tag in the list :)

docs/reference/query-languages/esql/_snippets/lists/mv-functions.md

Co-authored-by: Liam Thompson <[email protected]>

markjhoy · 2025-12-16T15:31:49Z

Thanks for iterating on the docs! One tiny little thing left to add: an inline applies_to tag in the list :)

All set. Thanks for your help!

x-pack/plugin/esql/qa/testFixtures/src/main/resources/mv_intersection.csv-spec

markjhoy · 2025-12-16T16:40:35Z

buildkite test this

start of MV_INTERSECT ESQL function

605e268

elasticsearchmachine added the v9.3.0 label Dec 11, 2025

markjhoy added 5 commits December 11, 2025 11:38

start of unit tests; correct int tests

060a626

Merge branch 'main' into markjhoy/add_MV_INTERSECT_function_esql

a83eda4

fix unit tests; add dpcs

91129af

Merge branch 'main' into markjhoy/add_MV_INTERSECT_function_esql

e61a080

fix doc link

8740478

markjhoy added 5 commits December 11, 2025 16:58

set capabilities

e3318e2

use LinkedHashSet to maintain order

cb46069

cleanup tests; set preview

9ae8c26

clean up code; get examples in docs

bd0f8af

Merge branch 'main' into markjhoy/add_MV_INTERSECT_function_esql

dd7fd12

markjhoy added >enhancement :Analytics/ES|QL AKA ESQL Team:SearchOrg Meta label for the Search Org (Enterprise Search) labels Dec 12, 2025

markjhoy marked this pull request as ready for review December 12, 2025 14:25

markjhoy requested review from a team and ioanatia December 12, 2025 14:25

elasticsearchmachine added the Team:Analytics Meta label for analytical engine team (ESQL/Aggs/Geo) label Dec 12, 2025

Update docs/changelog/139379.yaml

f219f97

This was referenced Dec 12, 2025

Add MV_INTERSECTION function to ES|QL #139222

Closed

ESQL: Functions! #98545

Open

markjhoy commented Dec 12, 2025

View reviewed changes

markjhoy requested a review from a team December 12, 2025 15:03

Merge branch 'main' into markjhoy/add_MV_INTERSECT_function_esql

9253ba2

kderusso reviewed Dec 15, 2025

View reviewed changes

craigtaverner requested changes Dec 15, 2025

View reviewed changes

refactor name to MV_INTERSECTION

bbf4fc4

markjhoy changed the title ~~ES|QL: Add MV_INTERSECT Function~~ ES|QL: Add MV_INTERSECTION Function Dec 15, 2025

markjhoy added 3 commits December 15, 2025 11:08

clean up docs

1b50a31

Merge branch 'main' into markjhoy/add_MV_INTERSECT_function_esql

92f8907

convert to BinaryScalarFunction

19d3041

craigtaverner approved these changes Dec 15, 2025

View reviewed changes

markjhoy added 2 commits December 15, 2025 13:45

consistent type resolution mismatch error;geo test

f8308d3

add additional type IT tests

e7ed664

markjhoy requested review from ioanatia, kderusso and leemthompo December 15, 2025 21:02

markjhoy added 2 commits December 15, 2025 19:54

Merge branch 'main' into markjhoy/add_MV_INTERSECT_function_esql

c1e6221

fix missing examples in docs

5f41504

leemthompo approved these changes Dec 16, 2025

View reviewed changes

docs/reference/query-languages/esql/_snippets/lists/mv-functions.md Outdated Show resolved Hide resolved

set docs link for applies preview

a8f150c

Co-authored-by: Liam Thompson <[email protected]>

ioanatia approved these changes Dec 16, 2025

View reviewed changes

x-pack/plugin/esql/qa/testFixtures/src/main/resources/mv_intersection.csv-spec Show resolved Hide resolved

x-pack/plugin/esql/qa/testFixtures/src/main/resources/mv_intersection.csv-spec Show resolved Hide resolved

update tests to include index; single-value params

9023322

markjhoy added 2 commits December 16, 2025 11:40

Merge branch 'main' into markjhoy/add_MV_INTERSECT_function_esql

b7630df

Merge branch 'main' into markjhoy/add_MV_INTERSECT_function_esql

c57afa4

markjhoy enabled auto-merge (squash) December 16, 2025 18:55

[CI] Auto commit changes from spotless

e2cf652

markjhoy merged commit f78dee1 into elastic:main Dec 16, 2025
35 checks passed

ES|QL: Add MV_INTERSECTION Function #139379

ES|QL: Add MV_INTERSECTION Function #139379

Uh oh!

Conversation

markjhoy commented Dec 11, 2025 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

github-actions bot commented Dec 11, 2025 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

🔍 Preview links for changed docs

Uh oh!

github-actions bot commented Dec 11, 2025

ℹ️ Important: Docs version tagging

When to use applies_to tags:

What NOT to do:

🤔 Need help?

Uh oh!

elasticsearchmachine commented Dec 12, 2025

Uh oh!

elasticsearchmachine commented Dec 12, 2025

Uh oh!

elasticsearchmachine commented Dec 12, 2025

Uh oh!

markjhoy Dec 12, 2025

Choose a reason for hiding this comment

Uh oh!

leemthompo Dec 15, 2025

Choose a reason for hiding this comment

Uh oh!

kderusso left a comment

Choose a reason for hiding this comment

Uh oh!

kderusso Dec 15, 2025

Choose a reason for hiding this comment

Uh oh!

markjhoy Dec 15, 2025 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Choose a reason for hiding this comment

Uh oh!

craigtaverner commented Dec 15, 2025

Uh oh!

craigtaverner left a comment

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Uh oh!

Uh oh!

ioanatia commented Dec 15, 2025

Uh oh!

markjhoy commented Dec 15, 2025

Uh oh!

craigtaverner commented Dec 15, 2025

Uh oh!

craigtaverner left a comment

Choose a reason for hiding this comment

Uh oh!

leemthompo left a comment

Choose a reason for hiding this comment

Uh oh!

Uh oh!

markjhoy commented Dec 16, 2025

Uh oh!

Uh oh!

Uh oh!

markjhoy commented Dec 16, 2025

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

6 participants

markjhoy commented Dec 11, 2025 •

edited

Loading

github-actions bot commented Dec 11, 2025 •

edited

Loading

markjhoy Dec 15, 2025 •

edited

Loading