Field Pivoter

The Field Pivoter pivots data in a list, map, or list-map field and creates a record for each item in the field.

The Field Pivoter processor pivots the data in one field. To pivot additional fields or nested structures, use additional Field Pivoters.

When you configure the Field Pivoter, you specify the list, map, or list-map field to pivot and where to write the pivoted data. By default, the processor writes the pivoted data to the original field, but you can specify another field for the data.

You can also specify whether to include the existing fields in the resulting records and configure the action to take when the field does not exist.

Generated Records

When you pivot a field, the Field Pivoter creates a new record for each first-level item in the list or map. To pivot additional fields or nested structures, use additional Field Pivoters.

When pivoting a field, you can include the existing fields in the record or drop them, using only the pivoted data in the new records. You can specify where to write the pivoted data - in the original field or in a different field. You can also specify whether to save the field name of the first-level item in the pivoted field.

For example, say you want to pivot the Color_List data in the following set of records so you can update the unit costs based on color later in the pipeline:
Incoming data
Pen_Type Color_List Unit_Cost

ballpoint

black

blue

red

.10

highlighter

light blue

yellow

.20

felt tip

black

.15

Pivot to existing field, include existing data
If you use the Field Processor default to pivot the list in the Color_List field to the same field and include existing fields, Field Pivoter overwrites the list in the Color_List field with the pivoted data as follows:
Pen_Type Color_List Unit_Cost
ballpoint black .10
ballpoint blue .10
ballpoint red .10
highlighter light blue .20
highlighter

yellow

.20
felt tip

black

.15
Pivot to new field, include existing data
If you configure the processor to pivot the list to a new Color field and include the existing records, Field Pivoter generates the following records:
Pen_Type Color_List Color Unit_Cost

ballpoint

black

blue

red

black

.10

ballpoint

black

blue

red

blue

.10

ballpoint

black

blue

red

red

.10

highlighter

light blue

yellow

light blue

.20

highlighter

light blue

yellow

yellow

.20

felt tip

black

black

.15

Pivot to new field, include existing data, and include field name of the first-level item in the pivoted field
You can include the field name of the first-level item in the pivoted field in the new record. For example, let's say the Color_List field names the first-level items in the pivoted field value_n as follows:
  "Color_List": {
      "value_1": "black",
      "value_2": "blue",
      "value_3": "red"
    }
You can include the value_n field names as fields in the new record. If you configure the processor to pivot the list to a new Color_Value field, include the existing records, and include the field name of the first-level item in the Color_FieldName field, Field Pivoter generates the following records:
Pen_Type Color_List Color_Value Color_FieldName Unit_Cost

ballpoint

black

blue

red

black

value_1

.10

ballpoint

black

blue

red

blue

value_2

.10

ballpoint

black

blue

red

red

value_3

.10

highlighter

light blue

yellow

light blue

value_1

.20

highlighter

light blue

yellow

yellow

value_2

.20

felt tip

black

black

value_1

.15

Pivot to new field, drop existing data
If you pivot data to a new Color field without including the existing fields, Field Pivoter produces records with only the Color field. This choice doesn't make sense in this example, but can be useful when pivoting nested lists or maps or when you plan to enrich the data downstream:
Color
black
blue
red
light blue

yellow

black

Configuring a Field Pivoter

Configure a Field Pivoter to pivot data in a list, map, or list-map field and generate a record for each item in the field.

  1. In the Properties panel, on the General tab, configure the following properties:
    General Property Description
    Name Stage name.
    Description Optional description.
    Required Fields Fields that must include data for the record to be passed into the stage.
    Tip: You might include fields that the stage uses.

    Records that do not include all required fields are processed based on the error handling configured for the pipeline.

    Preconditions Conditions that must evaluate to TRUE to allow a record to enter the stage for processing. Click Add to create additional preconditions.

    Records that do not meet all preconditions are processed based on the error handling configured for the stage.

    On Record Error Error record handling for the stage:
    • Discard - Discards the record.
    • Send to Error - Sends the record to the pipeline for error handling.
    • Stop Pipeline - Stops the pipeline. Not valid for cluster pipelines.
  2. On the Field Pivot tab, configure the following properties:
    Field Pivoter Property Description
    Field to Pivot List, map, or list-map field to pivot.
    Copy All Fields Includes all existing fields in the generated records.
    Pivoted Items Path Field path to write the pivoted data. If the field exists, the processor overwrites any data in the field.
    Note: When not used, the processor writes pivoted data to the original field.
    Save Original Field Name Specifies whether to save the field name of the first-level item in the pivoted field. You can save the original field name when you include all existing fields in the generated records.
    Original Field Name Path Field path to write the field name of the first-level item in the pivoted field.
    Field Does Not Exist Action to take if the record does not include the specified field to pivot.