ES|QL MV_EXPAND command
Elastic Cloud Serverless projects are autoscaled environments, fully managed by Elastic and available on Elastic Cloud. Availability This functionality is generally available and ready for production usage. Serverless interfaces and procedures might differ from classic Elastic Stack deployments. The Elastic Stack includes Elastic's core products such as Elasticsearch, Kibana, Logstash, and Beats. Availability This functionality is generally available and ready for production usage. 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.
Serverless
Generally available
Stack
GA
9.4+
…
Generally available since 9.4
Preview from 9.0 to 9.3
The MV_EXPAND processing command expands multivalued columns into one row per
value, duplicating other columns.
MV_EXPAND column
column- The multivalued column to expand.
MV_EXPAND does not preserve the sort order from a preceding SORT. The output
rows can be in any order. To guarantee a certain ordering, place a SORT after
MV_EXPAND.
Additionally, if a WHERE clause follows MV_EXPAND, it may prevent the query
planner from merging a preceding SORT with a trailing LIMIT into an
efficient TopN operation. In such cases, the query will fail with a
validation exception: MV_EXPAND [MV_EXPAND field] cannot yet have an unbounded SORT [SORT field] before it: either move the SORT after it, or add a LIMIT after the SORT.
To work around this, add a LIMIT before MV_EXPAND:
FROM index | SORT field | LIMIT 1000 | MV_EXPAND mv_field | WHERE mv_field == "value"
The Elastic Stack includes Elastic's core products such as Elasticsearch, Kibana, Logstash, and Beats.
Availability
Planned
We plan to add this functionality in a future Elastic Stack update. Subject to changes.
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.
Elastic Cloud Serverless projects are autoscaled environments, fully managed by Elastic and available on Elastic Cloud.
Availability
Generally available
This functionality is generally available and ready for production usage.
Serverless interfaces and procedures might differ from classic Elastic Stack deployments.
A full-text search on the expanded column, such as MATCH, is a
runtime search:
it runs row by row, and analyzes an indexed text field with the standard analyzer instead of the analyzer in
its mapping.
ROW a=[1,2,3], b="b", j=["a","b"]
| MV_EXPAND a
| a:integer | b:keyword | j:keyword |
|---|---|---|
| 1 | b | ["a", "b"] |
| 2 | b | ["a", "b"] |
| 3 | b | ["a", "b"] |