JSON Dataset Plugin

Version

Application Scenario

As JavaScript grows in popularity, JSON data is increasingly adopted. Since the JSON data structure is flexible, you may fail to analyze JSON data together with other types of data due to structural inconsistency. You can install a JSON Dataset plugin in FineBI to address this issue. This document details how to use this plugin.

Function Description

The JSON Dataset plugin allows you to process JSON data for use in FineBI.

Installing the Plugin

You can obtain the JSON Dataset plugin at https://community.finereport.com/plugin/?id=132.

For details about how to install the plugin, see Plugin Management.

Defining a JSON Data Connection

1. Log in to the FineBI system as the admin, choose System Management > Data Connection > Data Connection Management, click Create, and select Data Connection to create a data connection, as shown in the following figure.

2. Click JSON and configure the information of the JSON data connection, as shown in the following figure.

FanRuan does not provide online JSON debugging. You can download the example JSON file used in this document: book.zip

Extract the book.json file, upload it to your own server, and generate a URL for online access.

Note:

1. After saving the configuration of the JSON data connection, you can select the data connection, click the xxx icon on the right, select Rename, and rename the data connection.

2. If the data volume is large, you need to modify the value of Max Connections in Connection Pool in Network Connection.

The following table details each setting item.

Setting Item	Description
URL	The request address used for entering JSON text. (HTTP and HTTPS with non-self-signed SSL certificates are supported.) The URL supports suffix parameters (for example, https://api.example.com/goods?page=1&size=20). Make sure to provide a default parameter for querying during design. The address supports all formulas except for report functions and formulas used in cell calculations. Note: Both the HTTP protocol and the HTTPS protocol with non-self-signed SSL certificates are supported.
Username and Password	You can enter the username and password for authentication (if necessary).
Request Type	The parameter passing methods differ between GET and POST methods. In a GET request, the parameter is appended to the URL, while in a POST request, the parameter is placed in the HTTP request body, requiring you to add the parameter manually.
Cache Validity	The default cache time is 0. Cluster environment is supported, which can effectively improve the JSON dataset efficiency.  Note: The function is only supported in the plugin of V9.3.3.
Common	Common parameters, such as GET and POST, are those placed in the request body.
Request Header	Header parameters are placed in the request header. Static parameters are typically passed via HTTP headers, as headers are automatically included in each request.
Encoding	If a file is not UTF-8 encoded, you need to select the corresponding encoding in Encoding. Otherwise, the text information cannot be parsed correctly.

Note:

1. In the plugin of V9.3.3, you can select Java (default) or JavaScript V8 as the script engine. The JavaScript engine is automatically used on machines that do not support JavaScript V8.

2. The type of parameter to be added depends on what type of parameters your server expects to receive.

3. You are advised to obtain the parameter using a JSON file instead of simply an API.

Creating a JSON Dataset

Choose System Management > Data Connection > Server Dataset > Create Dataset, name the dataset JSON Dataset, select JSON (the data connection created in previous steps) in Data Connection, enter the query statement $.store.book[*], and click Save, as shown in the following figure.

The following table introduces the setting items.

Setting Item	Description	Example in This Document
Dataset Name	You can customize the dataset name. The dataset name cannot be empty.	JSON Dataset
Data Connection Name	You can select the JSON data connection created previously.	JSON, namely the JSON data connection created in the section "Defining the JSON Data Connection"
Key Sort	The setting item is used to sort the obtained columns. Key Sort is disabled by default.	Default
Column Name Prefetch	Enabling it will cause FineBI to traverse all data first to extract column names, which can be used to handle JSON data with inconsistent structures, but may slightly reduce efficiency. Column Name Prefetch is disabled by   default.	Default
Query Statement	You can enter the query statement.	$.store.book[*] This statement is to query information about all books in the bookstore. The following figure shows the query result. Note: $.store.book[*] is a fixed referencing method of JSON, which is used to fetch all data of book in store.
		$.store.book[*].category This statement is to query data in the category column. The following figure shows the query result.
		$.store.book[?(@.price<10)] This statement is to query data of books whose price is less than 10. The following figure shows the query result.

Introducing the Parameter Passing Method

The following figure shows the differences between the three parameter passing methods.

For details about the parameter passing method, see the section "Parameter Passing Method" in JSON Dataset Plugin.

The JSON program dataset allows you to convert JSON data to a two-dimensional table with JavaScript. This method applies to all scenarios where JSON datasets cannot be directly used, allowing you to customize scripts based on your data structures to achieve the integration with reports.

Note:

To create and use JSON program datasets, you need to use the JAR package generated on 2019-03-13 or later and the plugin of 9.2.3 or later versions.

Creating a JSON Program Dataset

Log in to the FineBI system as the admin, choose System Management > Data Connection > Server Dataset > Create Dataset, name the dataset JSON Program Dataset, select JSON in Data Connection, enter the script statement, and click Save, as shown in the following figure.

Fetching All Data

You can use the following script statement to fetch all book data.

var books = $.store.book;
var rowCount = books.length;
console.log("Row Count: " + rowCount);
var table = [];
var column = [];
books.forEach(function(value, index) {   
var row = [];   
for (var key in value) {       
row.push(value[key]);       
column.push(key);   
}   
console.log("The row has " + row.length + " column(s)");   
table.push(row);
});
return merge(table, unique(column))

The following table describes the built-in functions and variables in the script.

The following figure shows the queried data.

Fetching Data with a Condition

You can use the following script statement to fetch data of books whose price is less than 10.

var books = $.store.book;
var rowCount = books.length;
var table = [];
var column = [];
books.forEach(function(value, index) {   
var row = [];   
if (value.price < 10) {     
for (var key in value) {       
row.push(value[key]);       
column.push(key);     
}     
table.push(row);   
}
});

return merge(table, unique(column));

The following figure shows the queried data.

Fetching a Specific Column

You can use the following script statement to fetch data of authors of all books.

var books = $.store.book;
var rowCount = books.length;
var table = [];
var column = ["author"];
books.forEach(function(value, index) {   
var row = [];   
row.push(value.author);   
table.push(row);
});


return merge(table, column);

The following figure shows the queried data.

1. Log in to the FineBI system as the admin, select a folder in Public Data, click Add Dataset, and select Database Table.

2. Click Server Dataset, select JSON Dataset and/or JSON Program Dataset, and click OK, as shown in the following figure.

Note:

If the JSON data contains multiple types of data, errors may occur during frontend usage. You are advised to process the data to ensure consistent data types.