Lateral View

LATERAL VIEW is the column-to-row syntax provided by Doris. Combined with generator functions (such as EXPLODE), it expands a collection-typed field in a row into multiple rows and joins the expanded result, treated as a virtual table, with the original row.

Applicable Scenarios

When a row of data contains an enumerable collection such as an array or list, and you want to split each element of the collection into a separate row for analysis in a SQL query, you can use LATERAL VIEW. Typical scenarios include:

• Expanding an array field in a row and outputting each element together with the other original columns.
• Performing aggregation, filtering, or joining with other tables on each element of the collection separately.
• Using the output of a generator function (such as EXPLODE, EXPLODE_SPLIT) as a virtual table in a query.

Syntax

LATERAL VIEW generator_function ( expression [, ...] ) table_identifier AS column_identifier [, ...]

Parameters

Parameter	Description
generator_function	A generator function, such as EXPLODE or EXPLODE_SPLIT.
table_identifier	The alias of the virtual table produced by generator_function.
column_identifier	The column alias used to name the output rows. The number of column aliases must match the number of columns returned by the generator function.

Usage Example

The following example shows how to use LATERAL VIEW to perform a column-to-row query.

1. Prepare the Data

Create a person table and insert several rows of test data:

CREATE TABLE `person` (
  `id` int(11) NULL,
  `name` text NULL,
  `age` int(11) NULL,
  `class` int(11) NULL,
  `address` text NULL
) ENGINE=OLAP
UNIQUE KEY(`id`)
COMMENT 'OLAP'
DISTRIBUTED BY HASH(`id`) BUCKETS 1
PROPERTIES (
"replication_allocation" = "tag.location.default: 1",
"in_memory" = "false",
"storage_format" = "V2",
"disable_auto_compaction" = "false"
);

INSERT INTO person VALUES
    (100, 'John', 30, 1, 'Street 1'),
    (200, 'Mary', NULL, 1, 'Street 2'),
    (300, 'Mike', 80, 3, 'Street 3'),
    (400, 'Dan', 50, 4, 'Street 4');

2. Run the LATERAL VIEW Query

Use LATERAL VIEW together with the EXPLODE function to perform a Cartesian expansion of each element in the array ARRAY(30, 60) with each row of the person table:

SELECT * FROM person
LATERAL VIEW EXPLODE(ARRAY(30, 60)) tableName AS c_age;

3. View the Result

The query result contains the combination of each row of the person table with each row generated by EXPLODE:

+------+------+------+-------+----------+-------+
| id   | name | age  | class | address  | c_age |
+------+------+------+-------+----------+-------+
|  100 | John |   30 |     1 | Street 1 |    30 |
|  100 | John |   30 |     1 | Street 1 |    60 |
|  200 | Mary | NULL |     1 | Street 2 |    30 |
|  200 | Mary | NULL |     1 | Street 2 |    60 |
|  300 | Mike |   80 |     3 | Street 3 |    30 |
|  300 | Mike |   80 |     3 | Street 3 |    60 |
|  400 | Dan  |   50 |     4 | Street 4 |    30 |
|  400 | Dan  |   50 |     4 | Street 4 |    60 |
+------+------+------+-------+----------+-------+
8 rows in set (0.12 sec)