How can I help you?
Complex JSON to flat JSON in JavaScript Pivot Table component
22 Jan 202623 minutes to read
Overview
The Typescript Pivot Table component requires data in flat JSON format for proper binding. This guide explains how to convert complex, nested JSON structures to flat JSON format and bind it to the pivot table.
Understanding complex vs flat JSON
Complex JSON contains nested objects and arrays, making it difficult to directly bind to the pivot table. For example:
{
"CustomerID": "VINET",
"Freight": 32.38,
"OrderDetails": [
{
"OrderID": 10248,
"OrderDate": "1996-07-04T10:10:00.000Z"
}
],
"ShipDetails": [
{
"ShipName": "Vins et alcools Chevalier",
"ShipAddress": "59 rue de l'Abbaye",
"ShipCity": "Reims",
"ShipRegion": null,
"ShipCountry": "France",
"ShippedDate": "1996-07-16T12:20:00.000Z"
}
]
}Flat JSON has a simple key-value structure without nesting, which is suitable for pivot table binding:
{
"CustomerID": "VINET",
"Freight": 32.38,
"OrderID": 10248,
"OrderDate": "1996-07-04T10:10:00.000Z",
"ShipName": "Vins et alcools Chevalier",
"ShipAddress": "59 rue de l'Abbaye",
"ShipCity": "Reims",
"ShipRegion": null,
"ShipCountry": "France",
"ShippedDate": "1996-07-16T12:20:00.000Z"
}Implementation
You can convert complex JSON to flat JSON programmatically and bind it to the pivot table using the dataSource property in the load event.
In the following example, the complexToFlatJson() method is used to convert complex JSON to flat JSON and bind it to the pivot table using the dataSource property, then modifying the field names in the rows and columns based on the converted flat JSON under dataSourceSettings in the load event.
import { PivotView, FieldList, LoadEventArgs } from '@syncfusion/ej2-pivotview';
import { enableRipple } from '@syncfusion/ej2-base';
enableRipple(false);
PivotView.Inject(FieldList);
let parentProp: any = {};
let dataSource: Object[][];
let pivotGridObj: PivotView = new PivotView({
dataSourceSettings: {
expandAll: true,
enableSorting: true,
dataSource: data() as any,
columns: [{ name: 'OrderDetails' }],
values: [{ name: 'Freight', caption: 'Units Sold' }],
rows: [{ name: 'ShipDetails' }],
valueSortSettings: { headerDelimiter: ' - ' },
formatSettings: [{ name: 'Amount', format: 'C0' }]
},
load: function (args: LoadEventArgs) {
dataSource = JSON.parse(JSON.stringify(args.dataSourceSettings.dataSource));
args.dataSourceSettings.dataSource = complexToFlatJson(dataSource);
let rows: any = [];
for (let i: number = 0; i < args.dataSourceSettings.rows.length; i++) {
if (args.dataSourceSettings.rows[i].name in parentProp) {
rows = rows.concat(parentProp[args.dataSourceSettings.rows[i].name]);
} else {
rows.push(args.dataSourceSettings.rows[i]);
}
}
args.dataSourceSettings.rows = rows;
let columns: any = [];
for (let i: number = 0; i < args.dataSourceSettings.columns.length; i++) {
if (args.dataSourceSettings.columns[i].name in parentProp) {
columns = columns.concat(
parentProp[args.dataSourceSettings.columns[i].name]
);
} else {
columns.push(args.dataSourceSettings.columns[i]);
}
}
args.dataSourceSettings.columns = columns;
},
showFieldList: true,
width: '100%',
height: 500,
gridSettings: { columnWidth: 140 },
});
pivotGridObj.appendTo('#PivotTable');
function complexToFlatJson(data: Object[][]) {
let flatArray: any = [];
let flatObject: any = {};
for (let index = 0; index < data.length; index++) {
for (let prop in data[index]) {
let value: Object = data[index][prop];
if (Array.isArray(value)) {
for (let i: number = 0; i < value.length; i++) {
let childProp: any = [];
for (let inProp in value[i]) {
flatObject[inProp] = value[i][inProp];
let object = {
name: inProp,
};
childProp.push(object);
}
parentProp[prop] = childProp;
}
} else {
flatObject[prop] = value;
}
}
flatArray.push(flatObject);
flatObject = {};
}
return flatArray;
}
function data() {
return [
{
CustomerID: 'VINET',
Freight: 32.38,
OrderDetails: [
{
OrderID: 10248,
OrderDate: '1996-07-04T10:10:00.000Z',
}
],
ShipDetails: [
{
ShipName: 'Vins et alcools Chevalier',
ShipAddress: "59 rue de l'Abbaye",
ShipCity: 'Reims',
ShipRegion: null,
ShipCountry: 'France',
ShippedDate: '1996-07-16T12:20:00.000Z',
}
]
},
{
CustomerID: 'GALED',
Freight: 10.14,
OrderDetails: [
{
OrderID: 10366,
OrderDate: '1996-11-28T00:00:00.000Z',
}
],
ShipDetails: [
{
ShippedDate: '1996-12-30T00:00:00.000Z',
ShipName: 'Galería del gastronómo',
ShipAddress: 'Rambla de Cataluña, 23',
ShipCity: 'Barcelona',
ShipRegion: null,
ShipCountry: 'Spain',
}
]
},
{
CustomerID: 'VAFFE',
Freight: 13.55,
OrderDetails: [
{
OrderID: 10367,
OrderDate: '1996-12-02T00:00:00.000Z',
}
],
ShipDetails: [
{
ShippedDate: '1996-12-30T00:00:00.000Z',
ShipName: 'Vaffeljernet',
ShipAddress: 'Smagsloget 45',
ShipCity: 'Århus',
ShipRegion: null,
ShipCountry: 'Denmark',
}
]
},
{
CustomerID: 'ERNSH',
Freight: 101.95,
OrderDetails: [
{
OrderID: 10368,
OrderDate: '1996-11-29T00:00:00.000Z',
}
],
ShipDetails: [
{
ShippedDate: '1996-12-30T00:00:00.000Z',
ShipName: 'Ernst Handel',
ShipAddress: 'Kirchgasse 6',
ShipCity: 'Graz',
ShipRegion: null,
ShipCountry: 'Austria',
}
]
},
{
CustomerID: 'SPLIR',
Freight: 195.68,
OrderDetails: [
{
OrderID: 10369,
OrderDate: '1996-11-28T00:00:00.000Z',
}
],
ShipDetails: [
{
ShippedDate: '1996-12-30T00:00:00.000Z',
ShipName: 'Split Rail Beer & Ale',
ShipAddress: 'P.O. Box 555',
ShipCity: 'Lander',
ShipRegion: 'WY',
ShipCountry: 'USA',
},
]
}
];
}<!DOCTYPE html>
<html lang="en">
<head>
<title>EJ2 Pivot Grid</title>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<meta name="description" content="Typescript Pivot Grid Control" />
<meta name="author" content="Syncfusion" />
<link href="index.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-base/styles/tailwind3.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-buttons/styles/tailwind3.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-calendars/styles/tailwind3.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-dropdowns/styles/tailwind3.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-grids/styles/tailwind3.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-charts/styles/tailwind3.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-inputs/styles/tailwind3.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-lists/styles/tailwind3.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-navigations/styles/tailwind3.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-popups/styles/tailwind3.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-pivotview/styles/tailwind3.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-splitbuttons/styles/tailwind3.css" rel="stylesheet" />
<script src="https://cdnjs.cloudflare.com/ajax/libs/systemjs/0.19.38/system.js"></script>
<link href="https://maxcdn.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css" rel="stylesheet" />
<script src="systemjs.config.js"></script>
</head>
<body>
<div id='loader'>Loading....</div>
<div id='container'>
<div>
<div id='PivotTable'></div>
</div>
</div>
</body>
</html>