Overview
The Pivot Operation is used to transpose selected key-value pairs from an array within a JSON structure and promote them to the root level of an object. This operation simplifies data access by converting nested attribute arrays into flat key-value pairs.
It is commonly used when attribute-based data must be transformed into a structured format suitable for analytics, reporting, or downstream system integration.
When to Use
Use the Pivot Operation when JSON data contains dynamic attributes stored in arrays that need to be converted into top-level fields.
- When transforming attribute arrays into flat objects
- When preparing data for database insertion
- When normalizing API responses
- When simplifying nested JSON structures
- When improving data readability and accessibility
How It Works
The Pivot Operation extracts specific keys and values from an array and maps them as new key-value pairs at the root level.
- Identifies the array using the Get Key parameter
- Extracts the key name from each object
- Extracts the corresponding value
- Creates new root-level key-value pairs
- Preserves the original array
This process does not modify the original array and only appends new fields.
Pivot Operation Parameters
The following parameters are required for configuring the Pivot Operation.
| Parameter | Description |
|---|---|
| Get Key | Specifies the key that contains the array data |
| Transposed Key Name | Specifies the key whose value becomes the new field name |
| Transposed Value | Specifies the key whose value becomes the new field value |
Note: All three parameters are mandatory and must be configured.
Example Scenario
Consider the following input dataset containing an array of attributes.
Input Data
{
"bizdata_dataset": {
"id": 123,
"name": "sample",
"lastname": "dataset",
"attributes": [
{
"attributename": "item",
"attributevalue": "27",
"attribute_code": 12234
},
{
"attributename": "item2",
"attributevalue": "47",
"attribute_code": 12334
},
{
"attributename": "item1",
"attributevalue": "37",
"attribute_code": 13234
}
]
}
}
Configuration Example
Configure the Pivot Operation with the following parameters.
Get Key
attributes
Transposed Key Name
attributename
Transposed Value
attributevalue
Sample Output
After applying the Pivot Operation, the dataset is transformed as follows.
{
"bizdata_dataset": {
"id": 123,
"name": "sample",
"lastname": "dataset",
"attributes": [
{
"attributename": "item",
"attributevalue": "27",
"attribute_code": 12234
},
{
"attributename": "item2",
"attributevalue": "47",
"attribute_code": 12334
},
{
"attributename": "item1",
"attributevalue": "37",
"attribute_code": 13234
}
],
"item": "27",
"item2": "47",
"item1": "37"
}
}
Explanation of Output
The output reflects the following transformations.
- The original attributes array remains unchanged.
- The attributename values are promoted as root-level keys.
- The corresponding attributevalue values become their values.
- New fields are appended to the main dataset object.
Troubleshooting
- No Output Generated: Verify that all three parameters are configured.
- Missing Fields: Confirm that key names exist in the source array.
- Incorrect Mapping: Review Transposed Key and Value settings.
- Empty Result: Ensure the Get Key references a valid array.
- Unexpected Values: Validate input JSON structure.
Frequently Asked Questions
What is the purpose of the Pivot Operation?
It converts nested attribute arrays into flat key-value pairs for easier data access.
Does the Pivot Operation remove the original array?
No. The original array remains unchanged, and new fields are added.
Are all parameters mandatory?
Yes. Get Key, Transposed Key Name, and Transposed Value must all be provided.
Can I pivot multiple arrays?
No. Each Pivot Operation processes one array per configuration.
Is the operation suitable for database preparation?
Yes. It helps normalize dynamic attributes for structured storage.
Notes
- Always validate input JSON before applying Pivot.
- Use consistent attribute naming conventions.
- Test transformations in preview mode.
- Review output before production deployment.
- Combine Pivot with other operations for advanced processing.
This guide demonstrates how to use the Pivot Operation to transform nested JSON attributes into structured, flat key-value pairs for efficient data processing.
