expand command - Amazon OpenSearch Service

expand command

Note

To see which AWS data source integrations support this PPL function, see Functions.

Use the expand command to flatten a field of type Array<Any> or Map<Any>, producing individual rows for each element or key-value pair.

Syntax

Use the following syntax:

expand <field> [As alias]
field

  • The field to be expanded (exploded).

  • The field must be of a supported type.

alias

  • Optional.

  • The name to be used instead of the original field name.

Usage guidelines

The expand command produces a row for each element in the specified array or map field, where:

  • Array elements become individual rows.

  • Map key-value pairs are broken into separate rows, with each key-value represented as a row.

  • When an alias is provided, the exploded values are represented under the alias instead of the original field name.

You can use this command in combination with other commands, such as stats, eval, and parse, to manipulate or extract data post-expansion.

Examples

  • source = table | expand employee | stats max(salary) as max by state, company

  • source = table | expand employee as worker | stats max(salary) as max by state, company

  • source = table | expand employee as worker | eval bonus = salary * 3 | fields worker, bonus

  • source = table | expand employee | parse description '(?<email>.+@.+)' | fields employee, email

  • source = table | eval array=json_array(1, 2, 3) | expand array as uid | fields name, occupation, uid

  • source = table | expand multi_valueA as multiA | expand multi_valueB as multiB

You can use the expand command in combination with other commands such as eval, stats, and more. Using multiple expand commands will create a Cartesian product of all the internal elements within each composite array or map.

Effective SQL push-down query

The expand command is translated into an equivalent SQL operation using LATERAL VIEW explode, allowing for efficient exploding of arrays or maps at the SQL query level.

SELECT customer exploded_productId
FROM table
LATERAL VIEW explode(productId) AS exploded_productId

The explode command offers the following functionality:

  • It is a column operation that returns a new column.

  • It creates a new row for every element in the exploded column.

  • Internal nulls are ignored as part of the exploded field (no row is created/exploded for null).