Skip to main content
The Flatten Variant transformation component unpacks arrays of semi-structured data, for example in a JSON file, into structured data in the form of columns and rows of data in a table. The component is especially useful when using a Custom Connector to access an API that returns data in JSON format. The input to this component should include one or more variant-type columns containing the semi-structured data that is to be unpacked. The component will operate on every suitable column in the input. Each element in the source semi-structured data can be mapped to a different column in the target table. For example, consider the following JSON structure, containing an array of three elements: 
{
    "name": "varchar",
    "png": "varchar",
    "alt": "varchar"
}
When data in this format is flattened, the three array elements can be mapped to three columns in the target table. You use the component’s Column Mapping property to define which element in the source array will be mapped to which column in the target table. For example, to map the above structure you might complete the Column Mapping dialog as follows:
ColumnPropertyTypeAlias
JSON-INPUTnameVARCHARName
JSON-INPUTpngVARCHARFlag
JSON-INPUTaltVARCHARAlt_Text
Note that JSON-INPUT in this example is the name of the column in our input that contains the raw JSON data. When we sample the data from the transformation, we will see something like the following table:
NameFlagAlt_Text
Cyprushttps://flagcdn.com/w320/cy.pngThe flag of Cyprus.
Somaliahttps://flagcdn.com/w320/so.pngThe flag of Somalia.
Venezuelahttps://flagcdn.com/w320/ve.pngThe flag of Venezuela.
If an element of the source data contains a nested array, the column it’s mapped to will contain the entire nested array as a single string, requiring a further step to extract that array’s elements into their own columns. Use the Column Flattens property for this.
For an alternative method of extracting semi-structured data, you can use the Extract Nested Data component.

​
Use case

The Flatten Variant component is used to flatten and extract fields from semi-structured data in JSON format. Some common uses for this include:
  • Taking data from an API that returns a nested JSON object, and putting it into the columns and rows of a table to allow further transformations.
  • Extracting nested data from sources like S3 logs, CloudTrail, or Kafka, making the data ready for analysis.
  • Preparing semi-structured JSON data for loading into relational tables without losing granularity.

​
Properties

​
Name
string
required
A human-readable name for the component.
​
Include Input Columns
boolean
required
Choose whether to include input columns in the output table.
​
Column Mapping
column editor
required
Specify each item in the source array that will be mapped to a column in the target table. For each item, complete the following fields in the Column Mapping dialog.
  • Column: Select the column in the source table that contains the raw semi-structured data.
  • Property: The name of the element within the semi-structured data that you want to map to an output column.
  • Type: The data type of the output column you are mapping the element to.
  • Alias: The name of the output column you are mapping the element to.
If your source data contains nested arrays, use the Column Flattens property to further extract the contents of those arrays into additional columns before mapping them here.You can use Grid Variables in this dialog if desired.
​
Column Flattens
column editor
required
If an element of the source data contains a nested array, the Column Flattens property will allow you to extract the nested array’s elements into individual elements that can then be mapped to columns in the Column Mapping property. For each nested element you want to extract, complete the following fields in the Column Flattens dialog.
  • Column: Select the column in the source table that contains the raw semi-structured data.
  • Property: The name of the element within the nested array that you want to map to a column.
  • Alias: The name of the column you are mapping the element to. The alias you create here can then be selected in the Column drop-down of the Column Mapping dialog.
  • Outer: 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 by the name you gave as the Property). Select No to completely omit these rows from the output, or Yes to generate an output row with NULL values.
  • Recursive: Select No if only the input element referenced by the Property field is to be expanded. Select Yes for the expansion to be performed on all sub-elements recursively.
  • Mode: Select whether the expansion should be performed on only Object elements, only Array elements, or Both types of element.
You can extract elements from any level of array nesting by using standard JSON dot notation to address the element in the Property field. To address a nested field, you specify a path to it by listing the elements containing it in the JSON structure, separating each element with a dot. For example, consider this nested structure:
{
    "country": {
        "flags": {
            "alt": "varchar",
            "png": "varchar"
        },
    },
}
To address the alt element of this data, you need to enter country.flags.alt in the Property field. Dot notation allows you to reach any level of nesting in the structure.You can use Grid Variables in this dialog if desired.