Back to homepage

Mapping object for JSON

The mapping object for JSON is specified as the first element of the JSON array. This object is used for setting data types, creating multilevel hierarchies, and changing captions.

Properties

NameTypeDescription
typeStringoptional The field’s data type. Possible values: 
  • "number"
  • "string"
  • "date"
  • "date string"
  • "year/month/day"
  • "year/quarter/month/day"
  • "datetime"
  • "time"
  • "month"
  • "weekday"
  • "level"
  • "hidden"
 Read more about available data types in JSON.
captionStringoptional The field’s caption.
hierarchyStringoptional The name of the hierarchy. Used for multilevel hierarchies. Set this property to make the field a level of the specified hierarchy. In this case, the type must be set to "level". 
parentStringoptional The caption of the parent hierarchy level. Used for multilevel hierarchies. In this case, the type must be set to "level". 
levelStringoptional The caption of the hierarchy level. Used for multilevel hierarchies. In this case, the type must be set to "level".

Examples

1) Setting the mapping object in a JSON array of objects:

[
  {
    "Product": {
      "type": "string"
    },
    "Price": {
      "type": "number"
    }
  },
  {
    "Product": "Apple",
    "Price": 2.50
  },
  {
    "Product": "Cherry",
    "Price": 5.25
  }
]

See the full example on CodePen.

2) Setting the mapping object in a JSON array of arrays:

[
  {
    "Product": {
      "type": "string"
    },
    "Price": {
      "type": "number"
    },
  },
  ["Apple", 2.50],
  ["Cherry", 5.25]
]

Try a live demo on CodePen.

See also

Using the mapping object for JSON, you can set data types for fields in your data source. 

Available data types

The following data types are available in JSON:

Some of these types can be auto-resolved by the component.

Automatic type resolution

If you omit the field’s type, the component automatically assigns the data type based on the field’s first value. The following data types can be auto-resolved:

See the example.

The results of the automatic resolution may be unexpected when:

  • The first field value is null. In this case, the field type cannot be resolved automatically. 
  • The field values are not formatted correctly. For example, a number was specified in the hexadecimal form instead of decimal.
  • The field type cannot be resolved automatically. For example, time values are processed as numbers if the "time" type is not explicitly specified.
  • The first field value is formatted differently from other values. For example, the first value is a number while others are strings.

Learn more about each field data type in the sections below.

Number

This field type is used to store numbers. 

Field values can contain digits, -, and + characters. Point . must be used as a decimal separator. Numbers in exponential notation are also supported. Examples of valid values: -20, 2.50, 1.0E+2.

To mark the field as a number, set the type property of the mapping object to "number".

Can be auto-resolved: yes (learn more).

Available aggregations: all.

String

This field type is used to store strings.

Field values must be enclosed in double quotes ". Special characters, such as line breaks or double quotes ", must be escaped using the backslash \. Examples of valid values: "Apple", "\"A-Z\" section".

To mark the field as a string, set the type property of the mapping object to "string".

Can be auto-resolved: yes (learn more).

Available aggregations: Count, Distinct Count.

Date

This field type is used to store dates.

Field values must be specified in one of the following formats:

  • ISO 8601. Examples: "2018-01-10" (date), "2018-01-10T08:14:00" (date and time), "2018-01-10T06:14:00Z" (date and time in UTC).
    If the values are specified in this format, the field type can be auto-resolved as the "date" type.
  • Unix timestamps in milliseconds. Example: 1515535200000 (timestamp of the "2018-01-10" date).
    If the values are specified in this format, the field type must be set explicitly as a date. 
  • JavaScript Date objects. Can be used only with inline JSON data. Example: new Date(2018, 1, 10) (creates the "2018-01-10" date).
    If the values are specified in this format, the field type can be auto-resolved as the "date" type.

To mark the field as a date, set the type property of the mapping object to one of the following:

  • "date" – dates are divided into 3 subfields: Year, Month, Day.
    Can be auto-resolved: yes, if values are specified in ISO 8601 or as JavaScript Date objects (learn more).
    Available aggregations: Count, Distinct Count.
    See the example.
  • "date string" – dates are displayed as strings in the "dd/MM/yyyy" format. The format can be changed using the datePattern option.
    Can be auto-resolved: no (learn more).
    Available aggregations: Min, Max, Count, Distinct Count.
  • "year/month/day" – dates are represented as the multilevel hierarchy: Year > Month > Day.
    Can be auto-resolved: no (learn more).
    Available aggregations: Count, Distinct Count.
    See the example.
  • "year/quarter/month/day" – dates are represented as the multilevel hierarchy: Year > Quarter > Month > Day.
    Can be auto-resolved: no (learn more).
    Available aggregations: Count, Distinct Count.
  • "datetime" – dates are displayed as strings in the "dd/MM/yyyy HH:mm:ss" format. The format can be changed using the dateTimePattern option.
    Can be auto-resolved: no (learn more).
    Available aggregations: Min, Max, Count, Distinct Count.
    See the example.

Time

This field type is used to store time intervals, such as duration.

Field values must be specified as a number of seconds. In the component, values are displayed in the "HH:mm:ss" format. Examples of valid values: 5400 (displayed as "01:30:00" in the component).

To mark the field as time, set the type property of the mapping object to "time". 

Can be auto-resolved: no (learn more).

Available aggregations: Min, Max, Count, Distinct Count.

See the example.

Month

This field type is used to store months. Natural sorting is applied to the field members: from January to December.

Field values must start with a capital letter. Full names and three-letter abbreviations of months are supported. Examples of valid values: "October", "Dec", "May".

To mark the field as months, set the type property of the mapping object to "month".

Can be auto-resolved: no (learn more).

Available aggregations: Count, Distinct Count.

Weekday

This field type is used to store the days of the week. Natural sorting is applied to the field members: from Sunday to Monday.

Field values must start with a capital letter. Full names and three-letter abbreviations of the days of the week are supported. Examples of valid values: "Monday", "Sun", "Friday".

To mark the field as a day of the week, set the type property of the mapping object to "weekday".

Can be auto-resolved: no (learn more).

Available aggregations: Count, Distinct Count.

Level

This field type means that a field is a level of a multilevel hierarchy.

To mark the field as a hierarchy level, set the type property of the mapping object to "level".

Can be auto-resolved: no (learn more).

Available aggregations: none.

See the example.

Hidden

This field type is used to hide fields from the report.

To mark the field as hidden, set the type property of the mapping object to "hidden". 

Can be auto-resolved: no (learn more).

Available aggregations: none.

Note. Another way to hide fields from the report is to omit them in the mapping object.

After formatting your JSON data and setting field data types, learn how to connect to the JSON data.

See also

Use the drill-down feature to drill into the different levels of a hierarchy. This operation helps to reveal more details of your data and get a more specific view of the hierarchies. Drill back up when you need to get a general view of the data.

Example

Starting from a country level, drill down to a city level that can be further drilled down to an address level. Drill up the levels to return to the previous view of the hierarchy.

Note that this feature can be used only with the multi-level hierarchies. Refer to the Data types in JSON and Data types in CSV articles for details on how to define levels in the data types.

Use the drag-and-drop feature to organize and restructure your report in real time.

Example

Drag and drop the hierarchies between the rows, columns and report filters right on the grid.

Drag and drop the fields to organize the report

Use the expand and collapse operations to analyze the data on different levels of detail. 

Example

Expand the Category hierarchy to display the members of the next (Business Type) hierarchy.

In a classic form:

Expand and collapse to the successive hierarchies (classic view)

In a compact form:

Expand and collapse to the successive hierarchies (compact view)

The main idea of the calculated values lies in giving a more complete picture of your data by measuring your data in a custom way.

To create a calculated value, define an expression on which the values are calculated and apply it to your data. It serves as a new aggregation function that you can change or delete at any moment.

Moreover, multiple calculated values can be created within one report.

To add a calculated value

  • Go to the Fields tab (menu_fields) on the Toolbar.
  • Click the Add calculated value button.
  • Type in the name of a formula in the input field.
  • Choose the fields to include in the formula, the aggregation functions to apply to these fields, and add the necessary operators.
  • Apply the changes.

NOTE: the Count and Unique Count functions can be applied only to the string fields.

Example

Let’s define a new Revenue measure. It is composed of Price and Quantity fields (Price * Quantity).

  • Type in a name of a new calculated value.
  • Drag the fields to the Formula area and add a multiplication operator to compose a product of two values.
  • Apply the changes. Now the new calculated value is placed at the bottom of the list. Drop it to the Values area to add it to the report.
How to add a calculated value

All calculated values are saved in the measures of the report’s slice.

To edit a calculated value

  • Go to the Fields tab (menu_fields) on the Toolbar.
  • Find the calculated value in the All fields box.
  • Click the Sigma editing button () next to the value’s name.
  • Edit the calculated value.
  • Apply the changes.

To remove a calculated value

  • Go to the Fields tab (menu_fields) on the Toolbar.
  • Find the calculated value in the All fields box.
  • Click the Sigma editing button () next to the value’s name.
  • Click the Remove button (✕).
  • Confirm the action.

Individual values

Note that there is the Calculate individual values checkbox. If it is checked, the formula is calculated based on the raw values. Otherwise, it is calculated using the aggregated values.

Find out the difference between the calculated and calculated individual values in the demo.

We hope your time using WebDataRocks Pivot Table has been productive and satisfying. WebDataRocks provides all the basic features for data visualization and analysis, such as connecting to JSON or CSV data sources and essential data aggregation functions.

In case you want to have more features, be it advanced customization or more data source options, we recommend looking at Flexmonster Pivot Table & Charts — a commercial advanced component developed by the same team.

Flexmonster has all of the WebDataRocks features and more, so you have nothing to lose by migrating to it. In addition, the number of changes required to move from WebDataRocks is minimal.

Continue with this page to discover all the Flexmonster features your project can benefit from👇

No data size limit

WebDataRocks works with JSON and CSV datasets no larger than 1 MB, which should satisfy the majority of use cases.

Flexmonster doesn’t set limits on the dataset’s size, so the maximum size depends only on the browser’s capabilities. Moreover, Flexmonster can connect to server-side data storage and load only the necessary data subsets into the browser, allowing you to work with much larger datasets.

More data source options

In addition to having no strict limit on the data size, Flexmonster offers a wider selection of data sources.

The following data sources are supported out of the box:

  • JSON
  • CSV
  • Microsoft Analyses Services
  • SQL databases
  • Elasticsearch
  • MongoDB

You can also connect Flexmonster Pivot to any data source using the custom data source API — Flexmonster’s communication protocol that helps you build a custom data source using your server.

See the full list of the data sources supported by Flexmonster.

Built-in pivot charts

Flexmonster offers one more exclusive feature — built-in pivot charts. This allows you to visualize data using the charts out of the box without integrating with third-party charting libraries.

Check out the video that shows how to work with Flexmonster Pivot Charts:

Advanced customer support

Another important aspect of Flexmonster is Rockstar Support — a team of technical specialists who are always ready to answer your questions or help you resolve issues. You can also check out the collection of resolved support tickets to find a solution for your case.

Other benefits

In addition to everything listed, Flexmonster provides other benefits:

Where do I start with Flexmonster?

Flexmonster offers a 30-day free trial requiring no payment details, so you can test Flexmonster’s functionality and decide whether it fits your project. Moreover, you can get a special discount as a WebDataRocks user if you decide to buy the product license. 

To smoothly move from WebDataRocks to Flexmonster, check out our migration guide.

If you want to see Flexmonster in action, watch this video for a detailed overview of Flexmonster Pivot and then try out the live demo:

In this guide, you can learn how to format your JSON data so WebDataRocks can process it.

WebDataRocks supports two JSON formats:

1) Array of objects. Each object in the array is an unordered collection of key-value pairs, where the key is a field name and the value is a field member. Example:

[
  {
    "Product": "Apple",
    "Price": 2.50
  },
  {
    "Product": "Cherry",
    "Price": 5.25
  }
]

2) Array of arrays. The first subarray contains field names, while other subarrays contain the respective field members. The field’s name and members must have the same position in the subarrays. Example:

[
  ["Product", "Price"],
  ["Apple", 2.50],
  ["Cherry", 5.25]
]

Regardless of what JSON format you choose, the first element of each array can be the mapping object for JSON. This object is used to set data types and create multilevel hierarchies.

See also

What is WebDataRocks

WebDataRocks is a free web reporting tool for data analysis and visualization.

It is written in JavaScript and is not constrained by any external framework. This simple but enterprise-featured web-based pivot grid can be added to your website, application, or project web page within minutes.

What does it offer

WebDataRocks easily displays your CSV or JSON data in an interactive pivot table, offers a number of data analysis features, and provides a reporting experience. You get real-time reporting of your data on any device you prefer (PC, tablet, mobile, etc.)

Who can use WebDataRocks

Our reporting tool would be a perfect fit if you:

  • Want to analyze data quickly.
  • Are looking for an online reporting tool.
  • Need a lightweight web pivot table.
  • Don’t know how to code or don’t want to.

Why is it cool

  • WebDataRocks easily embeds in your web page not requiring much programming skills.
  • You can save your time and expenses on integration as it’s already configured and ready to use.
  • Our tool has enterprise features for a real business, despite being costless.
  • It’s a powerful analytic tool with a well-designed ready-to-use modern UI.

The main benefit is that WebDataRocks is an absolutely free tool created by passionate data lovers from Flexmonster.

How can I get WebDataRocks and add it to my project

To make it simple for you we’ve prepared an integration tutorial that provides you with easy instructions on how to download WebDataRocks and add it to your website or application.

How can I use more advanced options

For those of our users who want to further explore WebDataRocks functionality, we prepared guides on the following topics:

Some thoughts from creators

We truly hope that you will enjoy all this experience with WebDataRocks. It’s created to help solve data analysis needs for different kinds of projects: start-ups, nonprofit, or commercial. So, if it will work for you, please share your success with others. Let data rock this world!

License

By downloading, installing, and/or using WebDataRocks, you agree with the terms of WebDataRocks End User License Agreement.

FusionCharts is a charting library that provides a wide range of interactive charts, maps, and graphs. This tutorial describes how to integrate WebDataRocks with FusionCharts and visualize your data from the component.

Supported chart and map types

Charts:

Maps:

  • maps/worldwithcountries (demo)

In case the chart you need is not on the list, you can implement a custom logic of data processing in the options.prepareDataFunction parameter of fusioncharts.getData().

Please follow the steps below to integrate your pivot table component with FusionCharts.

Step 1. Add WebDataRocks to your project

Step 1.1. Complete the integration guide. Your code of the component with WebDatarocks should look similar to the following:

    import { Component } from "@angular/core";
import { WebdatarocksPivotModule } from "@webdatarocks/ngx-webdatarocks";

@Component({
  selector: "app-root",
  standalone: true,
  imports: [WebdatarocksPivotModule],
  templateUrl: "./app.component.html",
  styleUrls: ["./app.component.css"]
})
export class AppComponent {
}
    <app-wbr-pivot
 [toolbar]="true">
</app-wbr-pivot>

    Step 1.2. Create a report for WebDataRocks — connect to the data source and define which fields should be displayed in rows, columns, and measures:

      import { Component } from "@angular/core";
import { WebdatarocksPivotModule } from "@webdatarocks/ngx-webdatarocks";

@Component({
  selector: "app-root",
  standalone: true,
  imports: [WebdatarocksPivotModule],
  templateUrl: "./app.component.html",
  styleUrls: ["./app.component.css"]
})
export class AppComponent {
  report = {
    dataSource: {
      filename: "https://cdn.webdatarocks.com/data/data.csv",
    },
    slice: {
      rows: [
        {
          uniqueName: "Country",
        },
      ],
      columns: [
        {
          uniqueName: "Business Type",
        },
        {
          uniqueName: "Measures",
        },
      ],
      measures: [
        {
          uniqueName: "Price",
          aggregation: "sum",
        },
      ],
    },
  };
}
      <app-wbr-pivot
 [toolbar]="true"
 [report]="report">
</app-wbr-pivot>

      The fields you’ve specified in the report will be shown on the chart.

      Step 2. Get a reference to the WebDataRocks instance

      Some of WebDataRocks methods and events are needed to create a chart. Using a reference to the WebDataRocks instance, we can access WebDataRocks API.

      Import WebdatarocksComponent and get a reference to the <app-wbr-pivot> instance using a template variable and the @ViewChild decorator:

        import { Component, ViewChild } from "@angular/core";
import { WebdatarocksPivotModule, WebdatarocksComponent } from "@webdatarocks/ngx-webdatarocks";

@Component({
  selector: "app-root",
  standalone: true,
  imports: [WebdatarocksPivotModule],
  templateUrl: "./app.component.html",
  styleUrls: ["./app.component.css"]
})
export class AppComponent {
  @ViewChild("pivot") pivotRef!: WebdatarocksComponent;

  // ...
}
        <app-wbr-pivot #pivot
 [toolbar]="true"
 [report]="report">
</app-wbr-pivot>

        Now it’s possible to interact with the component through this.pivotRef.webdatarocks.

        Note. If you are using the legacy @webdatarocks/ng-webdatarocks wrapper, import WebdatarocksComponent from it.

        Step 3. Add FusionCharts

        Step 3.1. Download FusionCharts and its wrapper for Angular:

        npm install fusioncharts angular-fusioncharts

        Step 3.2. Import FusionChartsModule and necessary FusionCharts dependencies:

          // Import FusionChartsModule for Angular
import { FusionChartsModule } from "angular-fusioncharts";
// Import the FusionCharts library
import * as FusionCharts from "fusioncharts";
// Import the chart module
import * as Charts from "fusioncharts/fusioncharts.charts";
// Import a FusionCharts theme
import * as FusionTheme from "fusioncharts/themes/fusioncharts.theme.fusion";

          Step 3.3. Pass the imported dependencies to FusionChartsModule:

            FusionChartsModule.fcRoot(FusionCharts, Charts, FusionTheme);

            Step 3.4. Add FusionChartsModule to the component’s imports array:

              @Component({
  selector: 'app-root',
  standalone: true,
  imports: [WebdatarocksPivotModule, FusionChartsModule],
  templateUrl: './app.component.html',
  styleUrl: './app.component.css'
})

              Step 3.5. In the HTML template of the component, create a chart using the fusioncharts element:

                <fusioncharts
 width="100%"
 height="450"
 type="mscolumn2d"
 dataFormat="json">
</fusioncharts>

                To learn more about configurations available for FusionCharts, please refer to the FusionCharts documentation.

                If you run the project now, the chart won’t show any data because WebDataRocks is not integrated with FusionCharts yet. We will connect them in the next step.

                Step 3.6. Ensure that @angular-devkit/build-angular:browser is specified as a builder in angular.json since FusionCharts is not compatible with @angular-devkit/build-angular:browser-esbuild.

                Your builder configurations in the angular.json file should be similar to the following:

                {
                  "$schema": "./node_modules/@angular/cli/lib/config/schema.json",
                  "version": 1,
                  "newProjectRoot": "projects",
                  "projects": {
                    "wdr-fusioncharts": {
                      "architect": {
                        "build": {
                          "builder": "@angular-devkit/build-angular:browser",
                          "options": {
                            "outputPath": "dist/wdr-fusioncharts",
                            "index": "src/index.html",
                            "main": "src/main.ts"
                          }
                        }
                      }
                    }
                  }
                }

                Step 4. Show the data from the pivot table on the chart

                Step 4.1. Create a variable to store the chart’s data source (e.g., chartDataSource):

                  chartDataSource: Object = {};

                  The chartDataSource variable is now empty, but soon we’ll fill it with data from our component.

                  Step 4.2. Import the WebDataRocks Connector for FusionCharts:

                    import "@webdatarocks/webdatarocks/webdatarocks.fusioncharts.js";

                    The Connector provides the fusioncharts.getData() method, which gets data from WebDataRocks and converts it to the format required for a specific chart type.

                    Step 4.3. If we call the fusioncharts.getData() method before WebDataRocks is fully loaded, it will return an empty result. To know when WebDataRocks is ready to provide data for the chart, handle the reportcomplete event:

                      onReportComplete() {
  // Unsubscribing from reportcomplete
  // We need it only to track the initialization of WebDataRocks
  this.pivotRef.webDataRocks.off("reportComplete");
  this.createChart();
}
                      <app-wbr-pivot #pivotRef
 [toolbar]="true"
 [report]="report"
 (reportcomplete)="onReportComplete()">
</app-wbr-pivot>

                      Now we know when the data is loaded and the report is ready.

                      Step 4.4. Implement the createChart() function. It will use the fusioncharts.getData() method from the Connector:

                        createChart() {
  this.pivotRef.webDataRocks.fusioncharts?.getData(
    {
      type: "mscolumn2d",
    },
    // Function called when data for the chart is ready
    this.drawColumnChart.bind(this),
    // Function called on report changes (filtering, sorting, etc.)
    this.drawColumnChart.bind(this)
  );
}

                        Notice the type configuration — it must correspond to the chart type specified in step 3.5 (in our case, it is "mscolumn2d").

                        If the Connector does not support the desired chart type or you need to preprocess the data differently, implement a function that handles the data processing and pass it to fusioncharts.getData() as the options.prepareDataFunction parameter.

                        Step 4.5. Now create a function to draw the chart:

                          drawColumnChart(chartConfig: any) {
  // Applying the chart theme
  chartConfig.chart.theme = "fusion";
  // Updating the chart’s data source
  this.chartDataSource = chartConfig;
}

                          Step 4.6. Finally, pass the configured data source to the chart:

                            <fusioncharts
 width="100%"
 height="450"
 type="mscolumn2d"
 dataFormat="json"
 [dataSource]="chartDataSource">
</fusioncharts>

                            Since the dataSource property is bound to chartDataSource, the chart will be re-rendered each time the chartDataSource’s value is updated. As a result, all your changes to the WebDataRocks report will be reflected on the chart.

                            Step 5. Run the project

                            Run your project with the following command:

                            ng serve --open

                            If the page with WebDataRocks is not opened automatically, go to http://localhost:4200/ in the browser to see an interactive dashboard with WebDataRocks and FusionCharts. The column chart shows the data from WebDataRocks and reacts instantly to any changes in the report.

                            Check out the full code

                            After completing this tutorial, the full code of the component should look as follows:

                              import { Component, ViewChild } from "@angular/core";
import { WebdatarocksPivotModule, WebdatarocksComponent } from "@webdatarocks/ngx-webdatarocks";
import "@webdatarocks/webdatarocks/webdatarocks.fusioncharts.js";

import { FusionChartsModule } from "angular-fusioncharts";
import * as FusionCharts from "fusioncharts";
import * as Charts from "fusioncharts/fusioncharts.charts";
import * as FusionTheme from "fusioncharts/themes/fusioncharts.theme.fusion";

FusionChartsModule.fcRoot(FusionCharts, Charts, FusionTheme);

@Component({
  selector: "app-root",
  standalone: true,
  imports: [WebdatarocksPivotModule, FusionChartsModule],
  templateUrl: "./app.component.html",
  styleUrls: ["./app.component.css"],
})
export class AppComponent {
  @ViewChild("pivot") pivotRef!: WebdatarocksComponent;
  dataSource: Object = {};

  report = {
    dataSource: {
      filename: "https://cdn.webdatarocks.com/data/data.csv",
    },
    slice: {
      rows: [
        {
          uniqueName: "Country",
        },
      ],
      columns: [
        {
          uniqueName: "Business Type",
        },
        {
          uniqueName: "Measures",
        },
      ],
      measures: [
        {
          uniqueName: "Price",
          aggregation: "sum",
        },
      ],
    },
  };

  onReportComplete() {
    this.pivotRef.webDataRocks.off("reportComplete");
    this.createChart();
  }

  createChart() {
    this.pivotRef.webDataRocks.fusioncharts?.getData(
      {
        type: "mscolumn2d",
      },
      this.drawColumnChart.bind(this),
      this.drawColumnChart.bind(this)
    );
  }

  drawColumnChart(chartConfig: any) {
    chartConfig.chart.theme = "fusion";
    this.dataSource = chartConfig;
  }
}
                              <app-wbr-pivot #pivot
 [toolbar]="true"
 [report]="report"
 (reportcomplete)="onReportComplete()">
</app-wbr-pivot>
<fusioncharts
 width="100%"
 height="450"
 type="mscolumn2d"
 dataFormat="json"
 [dataSource]="dataSource"
></fusioncharts>

                              See also

                              Move up