Skip to Content
DocumentationTransformsExtract Root-Level JSON Fields

Extract Root-Level Fields from JSON Object

Extracts each key and value at the root level of a JSON object into a new column, using the key as the column name and value as the cell content.

Note

This transform only extracts fields from the top-level of the JSON object. To parse all properties, please see Flatten JSON Object

Example

data

{"a": "value_1", "b":"value_2"}
{"a": "value_3", "b":"value_4"}

→

data_a

data_b

value_1value_2
value_3value_4

Configuration

Parse Type

JSON Object should be selected for this transform.

Column

Select a column to parse as JSON arrays.

Output Column Prefix (Optional)

Specify a prefix for the column names.

Discover Additional Fields

Enable this to automatically include new fields that’s discovered during processing of your data. More information

Parsed Fields

Specify the type for each new column and uncheck the fields to exclude.

Note

Unchecking requires Discover Additional Fields to be off

Screen Shot of Flatten JSON Object transform

Adding the Transform

  1. Select a column with JSON object data by clicking its column header in the table.
  2. Find Flatten JSON Object transform under JSON group.

Alternatively, you can click on + Add Script Step on the right of the wrangler to find Flatten JSON Object under JSON group.

Screen Recording of adding the Flatten JSON Object transform

Discover Additional Fields

The keys that make up a JSON object are often not all present in the wrangling sample. Other times, keys are added to the JSON object while the pipeline is operational. In these cases, by selecting Discover Additional Fields, these keys are discovered automatically. Etleap will notify you about new JSON keys when they’re found.

If your pipeline is configured for automatic schema changes , Etleap will automatically adapt the script to include the new JSON keys, and the destination schema to include the new columns. On the other hand, if your pipeline is configured for manual schema changes , the pipeline will stop and you can go the wrangler to see a sample containing the new keys and approve of the adjusted the script that includes columns for the new keys.

With Discover Additional Fields enabled, if there is a key you don’t want to include as a column, simply drop the column.

Key Considerations

  • This transform replaces the original columns with new columns.
  • Currently, this transform only supports extracting the first level of an JSON object. To get nested fields extracted, use another Flatten JSON Object transform on the output columns
  • The Wide Integer type is deprecated, and scripts that use this type are not supported by API v2  endpoints. We recommend using DECIMAL(20,0) instead.
Extract JSON Values using JMESPathExtract Text