Use the unnest() function to explode the array column to multiple rows.
Required Parameter
- array_col
- Specifies the array column to be exploded.
Optional Parameters
- key_col
- Specifies the column to be used as key while exploding the array column.If 'key_col' is not specified, teradataml associates each array with a temporary generated unique key to identify exploded array elements.
- ordinality
- Specifies whether to include the position of each element in the array.
Default value: False
- kwargs
- array_col_alias
- Specifies the alias names for the output exploded array column.
Default Value: "array_col"
- key_col_alias
- Specifies the alias name for the output key column.
Default value: "key_col"
- pos_col_alias
- Specifies the alias name for the output position column.If 'ordinality' is set to False, then 'pos_col_alias' will be ignored.
Default value: "pos_col"
Example setup
The examples use the 'array_table' dataset.
>>> from teradataml import *
>>> load_example_data("array", "array_table")
Create a DataFrame on 'array_table'.
>>> df = DataFrame("array_table")
>>> df
Output
arr1 arr2 arr3
id
1 (10,20,30,40,50) (23,200,215,40,21) ('ab','bc','cd','ab','ef')
4 (180,28,38,48,58) (30,NULL,NULL,250,27) ('mn','no',NULL,'op','pq')
2 (150,25,35,45,55) (28,50,95,90,26) ('xy','yz','za','ab','xy')
6 (16,261,36,46,56) (29,170,160,46,25) ('uv','xy','ab','xy','yz')
3 (12,22,320,42,52) (25,22,140,200,23) ('pq','ab','ab','st','tu')
5 (14,24,34,44,54) (26,NULL,NULL,180,24) ('ij','jk',NULL,'kl','lm')
Example 1: Explode the array column 'arr1' without key column and ordinality
>>> res = df.unnest(array_col="arr1") >>> res
Output
id arr1 arr2 arr3 key_col array_col
0 2 (150,25,35,45,55) (28,50,95,90,26) ('xy','yz','za','ab','xy') 1 45
1 2 (150,25,35,45,55) (28,50,95,90,26) ('xy','yz','za','ab','xy') 1 25
2 2 (150,25,35,45,55) (28,50,95,90,26) ('xy','yz','za','ab','xy') 1 35
3 2 (150,25,35,45,55) (28,50,95,90,26) ('xy','yz','za','ab','xy') 1 150
4 2 (150,25,35,45,55) (28,50,95,90,26) ('xy','yz','za','ab','xy') 1 55
5 6 (16,261,36,46,56) (29,170,160,46,25) ('uv','xy','ab','xy','yz') 2 36
6 6 (16,261,36,46,56) (29,170,160,46,25) ('uv','xy','ab','xy','yz') 2 46
7 6 (16,261,36,46,56) (29,170,160,46,25) ('uv','xy','ab','xy','yz') 2 261
8 6 (16,261,36,46,56) (29,170,160,46,25) ('uv','xy','ab','xy','yz') 2 16
9 6 (16,261,36,46,56) (29,170,160,46,25) ('uv','xy','ab','xy','yz') 2 56
Example 2: Explode the array column 'arr3' with key column 'id' and ordinality
>>> res = df.unnest(array_col=df.arr3, key_col="id", ordinality=True)
Output
id arr1 arr2 arr3 key_col array_col pos_col
0 1 (10,20,30,40,50) (23,200,215,40,21) ('ab','bc','cd','ab','ef') 1 ab 4
1 1 (10,20,30,40,50) (23,200,215,40,21) ('ab','bc','cd','ab','ef') 1 ef 5
2 1 (10,20,30,40,50) (23,200,215,40,21) ('ab','bc','cd','ab','ef') 1 bc 2
3 1 (10,20,30,40,50) (23,200,215,40,21) ('ab','bc','cd','ab','ef') 1 ab 1
4 1 (10,20,30,40,50) (23,200,215,40,21) ('ab','bc','cd','ab','ef') 1 cd 3
5 2 (150,25,35,45,55) (28,50,95,90,26) ('xy','yz','za','ab','xy') 2 xy 5
6 2 (150,25,35,45,55) (28,50,95,90,26) ('xy','yz','za','ab','xy') 2 xy 1
7 2 (150,25,35,45,55) (28,50,95,90,26) ('xy','yz','za','ab','xy') 2 yz 2
8 2 (150,25,35,45,55) (28,50,95,90,26) ('xy','yz','za','ab','xy') 2 ab 4
9 2 (150,25,35,45,55) (28,50,95,90,26) ('xy','yz','za','ab','xy') 2 za 3
Example 3: Explode the array column 'arr2' with alias names specified
>>> res = df.unnest("arr2", df.id, True, array_col_alias="key", key_col_alias="val", pos_col_alias="pos")
>>> res
Output
id arr1 arr2 arr3 val key pos
0 1 (10,20,30,40,50) (23,200,215,40,21) ('ab','bc','cd','ab','ef') 1 40.0 4
1 1 (10,20,30,40,50) (23,200,215,40,21) ('ab','bc','cd','ab','ef') 1 23.0 1
2 1 (10,20,30,40,50) (23,200,215,40,21) ('ab','bc','cd','ab','ef') 1 215.0 3
3 1 (10,20,30,40,50) (23,200,215,40,21) ('ab','bc','cd','ab','ef') 1 200.0 2
4 1 (10,20,30,40,50) (23,200,215,40,21) ('ab','bc','cd','ab','ef') 1 21.0 5
5 2 (150,25,35,45,55) (28,50,95,90,26) ('xy','yz','za','ab','xy') 2 50.0 2
6 2 (150,25,35,45,55) (28,50,95,90,26) ('xy','yz','za','ab','xy') 2 26.0 5
7 2 (150,25,35,45,55) (28,50,95,90,26) ('xy','yz','za','ab','xy') 2 90.0 4
8 2 (150,25,35,45,55) (28,50,95,90,26) ('xy','yz','za','ab','xy') 2 28.0 1
9 2 (150,25,35,45,55) (28,50,95,90,26) ('xy','yz','za','ab','xy') 2 95.0 3