Use Lateral Join for column-to-row conversion
Column-to-row conversion is a common operation in ETL processing. Lateral is a special Join keyword that can associate a row with an internal subquery or table function. By using Lateral in conjunction with unnest(), you can expand one row into multiple rows. For more information, see unnest.
Limits
- Currently, Lateral Join is only used with unnest() to achieve column-to-row conversion. Other table functions and UDTFs will be supported later.
- Currently, Lateral Join does not support subqueries.
Use Lateral Join
Syntax:
from table_reference join [lateral] table_reference;
Examples:
SELECT student, score
FROM tests
CROSS JOIN LATERAL UNNEST(scores) AS t (score);
SELECT student, score
FROM tests, UNNEST(scores) AS t (score);
The second syntax here is a shortened version of the first one, where the Lateral keyword can be omitted using the UNNEST keyword. The UNNEST keyword is a table function that converts an array into multiple rows. Together with Lateral Join, it can implement common row expansion logic.
NOTE
If you want to perform unnest on multiple columns, you must specify an alias for each column, for example,
select v1, t1.unnest as v2, t2.unnest as v3 from lateral_test, unnest(v2) t1, unnest(v3) t2;
.
StarRocks supports type conversion among BITMAP, STRING, ARRAY, and Column.