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

`Pen_Type`	`Color_List`	`Unit_Cost`
`ballpoint`	`black` `blue` `red`	.`10`
`highlighter`	`light blue` `yellow`	.`20`
`felt tip`	`black`	.`15`

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 Processor

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.

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.

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.