After learning how to connect to a local or remote JSON data source, the next step is to configure its structure for successful data manipulation and building a report.
It is recommended to define the first object in a JSON array which serves for:
- Setting data types for specific fields to ensure the correct interpretation of the data.
- Creating multilevel hierarchies.
- Defining captions of the hierarchies.
Properties of the first object in a JSON array
| Name | Description |
|---|---|
type | Sets a field’s data type. Possible values are: |
| A string value. Available aggregations: Count and Distinct Count. The values of such a field type will be sorted alphabetically. |
| A numerical value. It is possible to apply any available aggregation function to this type of field. The values of such a field type will be sorted numerically. |
| This field type is used to define a separate level when creating a hierarchy. Use it along with hierarchy, level, and parent properties. See the example. |
| This field type is used for months. This field type sorts months in natural sorting order, not alphabetically. |
| This field type is used for days of the week. Natural sorting order for days will be applied then: from Sunday to Monday. |
| This field type is used for dates. It is applied to all dates by default. The dates are divided into 3 separate subfields: Year, Month, Day. See the example. |
| This field type is used for dates. The dates are displayed as strings and can be formatted using date pattern (default is dd/MM/yyyy). |
| This field type is used for dates. The following hierarchy will be applied: Year > Month > Day. See the example. |
| This field type is used for dates. The following hierarchy will be applied: Year > Quarter > Month > Day. |
| This field type is used for time that can be selected to values. It will be formatted according to HH:mm pattern.Available aggregations: Min, Max, Count, Distinct Count. Note that time should be specified in seconds. See the example. |
| This field type is used for dates that can be selected to values. By default, it will be formatted according to the dd/MM/yyyy HH:mm:ss pattern. Available aggregations: Min, Max, Count, Distinct Count.See the example. |
| This field type is used to hide a field from the Field List. |
caption | A caption of the hierarchy. |
hierarchy | A name of a hierarchy. Set it if the current field should be a level of the certain hierarchy (if "type": "level"). See the example. |
level | A caption of a hierarchy level (if "type": "level"). See the example. |
parent | A caption of a parent level (if "type": "level"). See the example. |
If the type of the data is not defined explicitly, the component determines the type of a field based on the first value of that field. Though the pivot table tries to guess the type correctly, if null is the first value, the results of data processing may be unpredictable. Setting types is a good practice before loading the data into the pivot table.
Supported types of JSON data
There are two possible types of a JSON dataset:
- An array of JSON objects.
See the following code snippet to understand better how the first object in a JSON array influences the pivot table’s interpretation of the data:var pivot = new WebDataRocks({
container: "wdr-component",
toolbar: true,
report: {
dataSource: {
data: getJSONData()
},
formats: [{
name: "calories",
maxDecimalPlaces: 2,
maxSymbols: 20,
textAlign: "right"
}],
slice: {
rows: [{
uniqueName: "Food"
}],
columns: [{
uniqueName: "[Measures]"
}],
measures: [{
uniqueName: "Calories",
aggregation: "average",
format: "calories"
}]
}
}
});
function getJSONData() {
return [{
"Category": {
type: "level",
hierarchy: "Food"
},
"Item": {
type: "level",
hierarchy: "Food",
level: "Dish",
parent: "Category"
},
"Serving Size": {
type: "level",
hierarchy: "Food",
level: "Size",
parent: "Dish"
},
"Calories": {
type: "number"
},
"Calories from Fat": {
type: "number"
}
},
{
"Category": "Breakfast",
"Item": "Frittata",
"Serving Size": "4.8 oz (136 g)",
"Calories": 300,
"Calories from Fat": 120
}];
} - A JSON array of arrays.
It is recommended to add the first object for setting data types and complex hierarchies.
The full example is on CodePen.
Undefined types in JSON
The definition of all fields’ data types is optional. It is possible to set only the necessary fields’ types and leave all others empty – {}. Rely on WebDataRocks – it will automatically pick up a data type for such fields.
If some fields’ data types need to be omitted at all, just do not include them into the first JSON object or set "type": "hidden".
See the example about undefined types of fields.
Supported date formats
The input values of dates have to be compliant with ISO 8601 – The International Standard for the representation of dates and time. It means that if the field’s date value is formatted according to this standard, the correct interpretation and processing of such fields are guaranteed.
For example, "2018-01-10" (date) or "2018-01-10T08:14:00" (date and time).
JavaScript Date objects and timestamps expressed in milliseconds are supported as well.
WebDataRocks Pivot Table supports 5 output date types:
"date""date string""year/month/day""year/quarter/month/day""datetime"
Creating multilevel hierarchies in JSON
JSON is the best to use when it comes to multilevel hierarchies. In JSON, you can create them from fields of all types.
Let’s see how to create a multilevel hierarchy based on the data below:
[
{
"Category": "Breakfast",
"Item": "Frittata",
"Serving Size": "4.8 oz (136 g)",
"Calories": 300
},
{
"Category": "Breakfast",
"Item": "Boiled eggs",
"Serving Size": "4.8 oz (135 g)",
"Calories": 250
}
]
We will create a Food hierarchy with the Category field as a first level, Item field as a second level, and Serving Size field as a third level.
The first step is to define the type of all the three fields as "level":
[
{
"Category": {
type: "level"
},
"Item": {
type: "level"
},
"Serving Size": {
type: "level"
},
"Calories": {
type: "number"
}
},
{
"Category": "Breakfast",
"Item": "Frittata",
"Serving Size": "4.8 oz (136 g)",
"Calories": 300
},
{
"Category": "Breakfast",
"Item": "Boiled eggs",
"Serving Size": "4.8 oz (135 g)",
"Calories": 250
}
]
Now we will use the hierarchy, parent, and level properties to create the Food hierarchy:
[
{
"Category": {
type: "level",
hierarchy: "Food"
},
"Item": {
type: "level",
hierarchy: "Food",
level: "Dish",
parent: "Category"
},
"Serving Size": {
type: "level",
hierarchy: "Food",
level: "Size",
parent: "Dish"
},
"Calories": {
type: "number"
}
},
{
"Category": "Breakfast",
"Item": "Frittata",
"Serving Size": "4.8 oz (136 g)",
"Calories": 300
},
{
"Category": "Breakfast",
"Item": "Boiled eggs",
"Serving Size": "4.8 oz (135 g)",
"Calories": 250
}
]
Check out how this dataset will be visualized in WebDataRocks:
You can see this demo on CodePen as well.