How to LATERAL VIEW explode

[christophediprima]

Hi there! I have seen discussions arround doing such things:

SELECT element
FROM t
LATERAL VIEW explode(elements) s AS element;

But couldn't find a proper example of how to do it... Do you guys have something?

[cpcloud]

This operation is spelled using t.my_column.unnest() or t.unnest("my_column").

TL; DR: start with the first form, and consider the second form if you need to generate array element offsets.

Here's a description of the two forms:

1. The first form is a column operation, that transforms an array column into a column with the expression of array element type. So, if the column type before calling unnest() is array<T>, then the type after calling unnest() is T. Here's the API documentation for that one: https://ibis-project.org/reference/expression-collections.html#ibis.expr.types.arrays.ArrayValue.unnest
2. The second form is slightly more advanced and produces a table instead of a new column. It's a cross join with the original table + the unnested column. This second form should be used if you need to generate offsets for the elements in the unnested array column. Here's the API docs for this one: https://ibis-project.org/reference/expression-tables#ibis.expr.types.relations.Table.unnest

[christophediprima]

Thanks a lot!