Skip to main content

EXAMPLE - Unnest an Array

This section describes how to unnest the values in an Array into separate columns in your dataset.

Source:

In the following example dataset, students took the same test three times, and their scores were stored in any array in the Scores column.

LastName

FirstName

Scores

Adams

Allen

[81,87,83,79]

Burns

Bonnie

[98,94,92,85]

Cannon

Chris

[88,81,85,78]

Transformation:

When the data is imported, you might have to re-type the Scores column as an array:

Transformation Name

Change column data type

Parameter: Columns

Scores

Parameter: New type

Array

You can now unnest the Scores column data into separate columns:

Transformation Name

Unnest Objects into columns

Parameter: Column

Scores

Parameter: Parameter: Paths to elements

[0]

Parameter: Parameter: Paths to elements

[1]

Parameter: Parameter: Paths to elements

[2]

Parameter: Parameter: Paths to elements

[3]

Parameter: Remove elements from original

true

Parameter: Include original column name

true

In the above transformation:

  • Each path is specified in a separate row.

    • The [x] syntax indicates that the path is the xth element of the array.

    • The first element of an array is referenced using [0].

  • You can choose to delete the element from the original or not. Deleting the element can be a helpful way of debugging your transformation. If all of the elements are gone, then the transformation is complete.

  • If you include the original column name in the output column names, you have some contextual information for the outputs.

Results:

LastName

FirstName

Scores_0

Scores_1

Scores_2

Scores_3

Adams

Allen

81

87

83

79

Burns

Bonnie

98

94

92

85

Cannon

Chris

88

81

85

78