Extract Structured Data

The Extract Structured Data transformation component unpacks arrays of structured data—for example in STRUCT or ARRAY data types—into columns and rows of data in a table.

This component is especially useful when using a Custom Connector to access an API that returns data in structured format. The flow into this component should include a single, variant-type column that is to be unpacked.

Note

This component won't unpack semi-structured data formats such as JSON.

Each element in the source structured data can be mapped to a different column in your target table. For example, consider the following array of three elements:

"name", "png", "alt"

When data in this format is extracted, each of the three array elements will be mapped to a different column in the target table. When we sample the data from the transformation, we will see something like the following table:

Name	Flag	Alt_Text
Cyprus	https://flagcdn.com/w320/cy.png	The flag of Cyprus.
Somalia	https://flagcdn.com/w320/so.png	The flag of Somalia.
Venezuela	https://flagcdn.com/w320/ve.png	The flag of Venezuela.

Use the Columns property to define which elements in the source array will be mapped to columns in the target data.

Note

For an alternative method of extracting semi-structured data, you can use the Flatten Variant component.
For Snowflake and Amazon Redshift projects, use the Extract Nested Data component instead.

Properties

Name = string

A human-readable name for the component.

Columns = data structure

Use this property to select which elements from the structured input will be mapped to columns in the output. The Columns dialog shows a graphical representation of every addressable element in the input. Each element has a corresponding checkbox. Select an element's checkbox to include that element in the output. No elements are selected by default.

To select every element, click Select all.
To deselect every element, click Clear all or Reset.

Click Save when you have finished editing and selecting elements.

Aliases = column editor

By default, the output columns will have the same names as the input elements. You can rename the output columns by specifying aliases in this dialog.

Source Column: Select the source element that you wish to provide an alias for.
Target Column: Provide a name for the output column.

You can use Grid Variables in this dialog if desired.

You can provide aliases for any or all input elements. This property is optional, however, and can be left empty if you don't wish to provide alternative names for any columns.

Include Input Columns = boolean

Choose whether to include input columns in the output.

Outer Join = boolean

Determines how to handle input rows that can't be expanded (for example, because they have no fields to expand, or because they can't be accessed). Select No to completely omit these rows from the output, or Yes to generate an output row with NULL values.

Snowflake	Databricks	Amazon Redshift
❌	✅	❌