Use LATERAL VIEW to split a row into multiple rows - MaxCompute

When you query arrays or maps in MaxCompute, a standard SELECT statement returns one row per input row — you cannot flatten nested data inline. LATERAL VIEW bridges this gap: it pairs each input row with the rows produced by a user-defined table-valued function (UDTF), letting you expand arrays and maps into queryable rows and then aggregate them.

How it works

A UDTF has a one-to-many relationship between input and output — one input row can produce many output rows. Because of this, you cannot call a UDTF directly in the SELECT list. LATERAL VIEW joins each input row with the UDTF output, effectively flattening nested data into a queryable table.

When you use LATERAL VIEW OUTER and the UDTF returns no rows (for example, when exploding an empty array), the original input row is retained in the result set. All columns that would have been generated by the UDTF are set to NULL.

Syntax

LATERAL VIEW [OUTER] <udtf_name>(<expression>) <table_alias> AS <column_alias> [, <column_alias> ...]

FROM <base_table> LATERAL VIEW ... [LATERAL VIEW ...]

Parameters

Parameter	Required	Description
`udtf_name`	Yes	The UDTF that splits a row into multiple rows. For available functions, see Other functions.
`expression`	Yes	The column whose data is split.
`table_alias`	Yes	The alias for the UDTF result set.
`column_alias`	Yes	The alias for each column produced by the split. Specify multiple aliases separated by commas.
`base_table`	Yes	The source table.

You can chain multiple LATERAL VIEW clauses after the FROM clause. Each subsequent LATERAL VIEW can reference all tables and columns defined before it, allowing you to expand multiple columns in a single query.

Sample data

The examples below use the pageAds table, which tracks which ads appear on each page of a website:

pageid	col1	col2
front_page	[1, 2, 3]	["a", "b", "c"]
contact_page	[3, 4, 5]	["d", "e", "f"]

Create and populate the table with the following statements:

CREATE TABLE pageAds (pageid STRING, col1 ARRAY<INT>, col2 ARRAY<STRING>);
INSERT INTO pageAds VALUES ('front_page', ARRAY(1,2,3), ARRAY("a","b","c"));
INSERT INTO pageAds VALUES ('contact_page', ARRAY(3,4,5), ARRAY("d","e","f"));

Examples

Expand an array column

Split col1 into individual rows, retaining the original col2 column as an array:

SELECT pageid, col1_new, col2
FROM pageAds LATERAL VIEW EXPLODE(col1) adTable AS col1_new;

Result:

+--------------+------------+---------------+
| pageid       | col1_new   | col2          |
+--------------+------------+---------------+
| front_page   | 1          | ["a","b","c"] |
| front_page   | 2          | ["a","b","c"] |
| front_page   | 3          | ["a","b","c"] |
| contact_page | 3          | ["d","e","f"] |
| contact_page | 4          | ["d","e","f"] |
| contact_page | 5          | ["d","e","f"] |
+--------------+------------+---------------+

Aggregate exploded values

Count how many times each value in col1 appears across all pages:

SELECT col1_new, COUNT(1) AS count
FROM pageAds LATERAL VIEW EXPLODE(col1) adTable AS col1_new
GROUP BY col1_new;

Result:

+------------+-------+
| col1_new   | count |
+------------+-------+
| 1          | 1     |
| 2          | 1     |
| 3          | 2     |
| 4          | 1     |
| 5          | 1     |
+------------+-------+

The value 3 has a count of 2 because it appears in both front_page and contact_page.

Expand multiple array columns

Chain two LATERAL VIEW clauses to expand col1 and col2 simultaneously. Multiple LATERAL VIEW clauses produce a cross-product of the expanded rows:

SELECT pageid, mycol1, mycol2
FROM pageAds
  LATERAL VIEW EXPLODE(col1) myTable1 AS mycol1
  LATERAL VIEW EXPLODE(col2) myTable2 AS mycol2;

Result:

+--------------+--------+--------+
| pageid       | mycol1 | mycol2 |
+--------------+--------+--------+
| front_page   | 1      | a      |
| front_page   | 1      | b      |
| front_page   | 1      | c      |
| front_page   | 2      | a      |
| front_page   | 2      | b      |
| front_page   | 2      | c      |
| front_page   | 3      | a      |
| front_page   | 3      | b      |
| front_page   | 3      | c      |
| contact_page | 3      | d      |
| contact_page | 3      | e      |
| contact_page | 3      | f      |
| contact_page | 4      | d      |
| contact_page | 4      | e      |
| contact_page | 4      | f      |
| contact_page | 5      | d      |
| contact_page | 5      | e      |
| contact_page | 5      | f      |
+--------------+--------+--------+

Each row in front_page has 3 values in col1 and 3 values in col2, producing 3x3 = 9 rows. The same applies to contact_page.

Handle empty arrays with LATERAL VIEW OUTER

Without OUTER, if the UDTF returns no rows (for example, when exploding an empty array), the original row is dropped from the result:

-- If a page has an empty col1 array, it disappears from the results
SELECT pageid, col1_new
FROM pageAds LATERAL VIEW EXPLODE(col1) adTable AS col1_new;

With LATERAL VIEW OUTER, the original row is retained and the generated columns are filled with NULL:

-- The page row is retained, with col1_new set to NULL
SELECT pageid, col1_new
FROM pageAds LATERAL VIEW OUTER EXPLODE(col1) adTable AS col1_new;

What's next

To transpose data between rows and columns using LATERAL VIEW, see Best practices for transposing rows and columns.