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 of multi-level hierarchies;
  • defining captions of the hierarchies.

Properties of the first object in a JSON array:

NameDescription
typeSets a field’s data type. Possible values are:
  • “string”

A string value. Available aggregations: Count and Distinct Count. The values of such a field type will be
sorted alphabetically.
  • “number”

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.
  • “level”

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
.
  • “month”

This field type is used for months. This field type sorts months in natural sorting order, not alphabetically.
  • “weekday”

This field type is used for days of the week. Natural sorting order for days will be applied then: from Sunday to Monday.
  • “date”

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.
  • “date string”

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).
  • “year/month/day”

This field type is used for dates. The following hierarchy will be applied: Year > Month > Day. See the
example
.
  • “year/quarter/month/day”

This field type is used for dates. The following hierarchy will be applied: Year > Quarter > Month > Day.
  • “time”

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.
  • “datetime”

This field type is used for dates that can be selected to values. By default, it will be formatted
according
to dd/MM/yyyy HH:mm:ss pattern. Available aggregations: Min, Max, Count, Distinct Count.
See the example.
  • “hidden”

This field type is used to hide a field from the Field List.
captionA caption of a hierarchy.
hierarchyA name of a hierarchy. Set it if the current field should be a level of the certain hierarchy (if "type": "level"). See the example.
levelA caption of a hierarchy level (if "type": "level"). See the example.
parentA 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:

  1. 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
            }
    ];
    }
    

    The full example is on CodePen.

  2. 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:

  1. “date”
  2. “date string”
  3. “year/month/day”
  4. “year/quarter/month/day”
  5. “datetime”