Pivot

Edit this page

The pivot transform maps unique values from a field to new aggregated fields (columns) in the output stream. The transform requires both a field to pivot on (providing new field names) and a field of values to aggregate to populate the new cells. In addition, any number of groupby fields can be provided to further subdivide the data into output data objects (rows).

Pivot transforms are useful for creating matrix or cross-tabulation data, acting as an inverse to the fold transform.

Pivot Transform Definition

Property Type Description
pivot String

Required. The data field to pivot on. The unique values of this field become new field names in the output stream.
value String

Required. The data field to populate pivoted fields. The aggregate values of this field become the values of the new pivoted fields.
groupby String[]

The optional data fields to group by. If not specified, a single group containing all data objects will be used.
limit Number

An optional parameter indicating the maximum number of pivoted fields to generate. The default (0) applies no limit. The pivoted pivot names are sorted in ascending order prior to enforcing the limit. Default value: 0
op String

The aggregation operation to apply to grouped value field values. Default value: sum

Usage

For the following input data:

[
  {"country": "Norway", "type": "gold", "count": 14},
  {"country": "Norway", "type": "silver", "count": 14},
  {"country": "Norway", "type": "bronze", "count": 11},
  {"country": "Germany", "type": "gold", "count": 14},
  {"country": "Germany", "type": "silver", "count": 10},
  {"country": "Germany", "type": "bronze", "count": 7},
  {"country": "Canada", "type": "gold", "count": 11},
  {"country": "Canada", "type": "silver", "count": 8},
  {"country": "Canada", "type": "bronze", "count": 10}
]

The pivot transform

{
  "pivot": "type",
  "groupby": ["country"],
  "value": "count"
}

produces the output:

[
  {"country": "Norway", "gold": 14, "silver": 14, "bronze": 11},
  {"country": "Germany", "gold": 14, "silver": 10, "bronze": 7},
  {"country": "Canada", "gold": 11, "silver": 8, "bronze": 10}
]

Example