Connecting to JSON

This article illustrates how to build a report based on a JSON data source. The most simple configuration to use a JSON data source is to display data that is already on your page. Alternatively, you can load JSON data from your local file system (see Connect > To local JSON in the Toolbar), refer to a remote JSON file, or add data generated by a server-side script.

To load big JSON files (more than 100 MB), we recommend using Flexmonster Data Server - our powerful tool for processing large datasets. Refer to this tutorial for details: Connecting to JSON using Flexmonster Data Server.

This guide contains the following sections:

Supported JSON formats
Connect to a sample JSON
Display non-English characters correctly

Supported JSON formats

The component supports two JSON formats – an array of objects, where each object is an unordered set of name/value pairs, and an array of arrays, where each subarray contains ordered values. See the array of objects sample below:

let jsonData = [
  {
    "Color" : "green",
    "Country" : "Canada",
    "State" : "Ontario",
    "City" : "Toronto",
    "Price" : 174,
    "Quantity" : 22
  },
  {
    "Color" : "red",
    "Country" : "USA",
    "State" : "California",
    "City" : "Los Angeles",
    "Price" : 166,
    "Quantity" : 19
  }
];

The following sample demonstrates what a JSON format with an array of arrays looks like:

let jsonData = [
  [
    "Category",
    "Color",
    "Price",
    "Quantity"
  ],
  ["Ice-cream", "red", 23.32, 4],
  ["Ice-cream", "yellow", 4.2, 3],
  ["Sweets", "red", 45.3, 2],
  ["Candies", "orange", 22.65, 7]
];

Connect to a sample JSON

To use JSON data as a data source in the pivot table embedded in your project, follow these steps:

Step 1. Embed Flexmonster into your project.

If Flexmonster is not yet embedded, set up an empty component in your webpage:

In pure JavaScript

Complete the Integrating Flexmonster guide. Your code should look similar to the following example:

let pivot = new Flexmonster({
  container: "pivotContainer",
  componentFolder: "node_modules/flexmonster/",
  toolbar: true
});

In Angular

Complete the Integration with Angular guide. Your code should look similar to the following example:

<fm-pivot
 [toolbar]="true">
</fm-pivot>

In React

Complete the Integration with React guide. Your code should look similar to the following example:

<FlexmonsterReact.Pivot
 toolbar={true}
/>

In Vue

Complete the Integration with Vue guide. Your code should look similar to the following example:

<Pivot
 toolbar
/>

Step 2. Copy the following JSON and paste it into your webpage:

let jsonData = [
  {
    "Color" : "green",
    "Country" : "Canada",
    "State" : "Ontario",
    "City" : "Toronto",
    "Price" : 174,
    "Quantity" : 22
  },
  {
    "Color" : "red",
    "Country" : "USA",
    "State" : "California",
    "City" : "Los Angeles",
    "Price" : 166,
    "Quantity" : 19
  }
];

let pivot = new Flexmonster({
  container: "pivotContainer",
  componentFolder: "node_modules/flexmonster/",
  toolbar: true 
});

Step 3. Add the data property to the ReportObject:

let jsonData = [
  {
    "Color" : "green",
    "Country" : "Canada",
    "State" : "Ontario",
    "City" : "Toronto",
    "Price" : 174,
    "Quantity" : 22
  },
  {
    "Color" : "red",
    "Country" : "USA",
    "State" : "California",
    "City" : "Los Angeles",
    "Price" : 166,
    "Quantity" : 19
  }
];

let pivot = new Flexmonster({
  container: "pivotContainer",
  componentFolder: "node_modules/flexmonster/",
  toolbar: true,
  report: {
    dataSource: {
      data: jsonData
    }
  }
});

Step 4. Define a slice — fields that go to rows, go to columns and go to measures:

let jsonData = [
  {
    "Color" : "green",
    "Country" : "Canada",
    "State" : "Ontario",
    "City" : "Toronto",
    "Price" : 174,
    "Quantity" : 22
  },
  {
    "Color" : "red",
    "Country" : "USA",
    "State" : "California",
    "City" : "Los Angeles",
    "Price" : 166,
    "Quantity" : 19
  }
];

let pivot = new Flexmonster({
  container: "pivotContainer",
  componentFolder: "node_modules/flexmonster/",
  toolbar: true,
  report: {
    dataSource: {
      data: jsonData
    },
    slice: {
      rows: [
        { uniqueName: "Color" },
        { uniqueName: "[Measures]" }
      ],
      columns: [
        { uniqueName: "Country" }
      ],
      measures: [
        {
          uniqueName: "Price",
          aggregation: "sum"
        }
      ]
    }
  }
});

Step 5. Launch the page from a browser — there you go! A pivot table based on JSON data is embedded into your project. Check out this example on JSFiddle.

Now you know how to build a report based on our sample JSON. This example shows how to use a JSON array of arrays. The next step for you is to visualize your JSON data.

Display non-English characters correctly

If you use an alphabet with special characters you must use UTF-8 encoding. There are two ways to do this:

Specify the encoding for your HTML page to UTF-8 using the content-type HTTP header or the corresponding meta tag:
```
<meta http-equiv="content-type" content="text/html; charset=utf-8"/>
```
Content from a database and static JSON files also must be encoded as UTF-8.
If you are not able to change the content of your HTML file, you can embed Flexmonster Pivot in a separate JS file with specified UTF-8 encoding.
```
<script src="yourfile.js" charset="UTF-8"></script>
```

What's next?

You may be interested in the following articles: