JSON Dataset Plugin

Version

Application Scenario

With the popularity of JavaScript, JSON-formatted data is also being used more and more widely. You can use the JSON Dataset plugin to convert JSON-formatted data to a data table that can be used in a report.

Grammar Rule

For details about syntax rules of JSON querying, see JsonPath.

Note:

The method of writing parameters with JsonPath syntax is the same as with syntax in regular datasets.

Scheme Comparison

Comparison Item	Current Plan	New Plan
Introduction	The method this document introduces. You need to download the JSON Dataset plugin in FineReport first.	This document does not introduce the method. Use the Parse JSON operator in FineDataLink.
Main implementation process	It is necessary to learn the syntax of JSON queries in advance. After creating a JSON dataset, enter a query statement to fetch the data. Fetch the data from the category column using the sample data source in this document.	You can use the Parse JSON operator to directly visualize and select nodes without entering a query statement. Fetch the data from the category column using the sample data source in this document.

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

For details about installing the plugin in the designer, see Designer Plugin Management.

For details about installing the plugin on the server, see Server Plugin Management.

Defining a JSON Data Connection

After installing the plugin, choose Server > Define Data Connection on the designer menu bar and create a new JSON data connection JSON.

Enter the URL http://fine-doc.oss-cn-shanghai.aliyuncs.com/book.json, configure the other settings as shown in the following figure, and click OK.

Note:

The text information returned by the request address can be any text in JSON format.

The following table describes each setting item.

Setting	Definition
URL	The request address is used for entering JSON text. (HTTP and HTTPS with non-self-signed SSL certificates are supported.) Just like the dataset, the address supports parameters. 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: You are advised to use a simple interface link when fetching parameters because the .json file cannot pass parameters.
Username and Password	Used for entering authentication information, which can be empty. Only authentication in the form of Authorization: Basic Base64(username:password) is supported.
Cache Validity	Used for setting the validity period of the cache (in milliseconds). The default value is 0, which means no caching. After you set the cache time, caching will be performed after the initial connection (supports clustering). The results read within the cache validity period will be the cache content and will not change with the variation of the requested interface data.
Request Type	Used for setting different request types and parameter passing according to different request types. The default request type is GET, which can be modified to POST_FORM or POST_RAW. For details about request types, see section "Parameter Transferring Method."
Common	Common parameters are those placed in the request body. GET and POST are common parameters. The common parameter supports addition, deletion, and modification. Besides, you can refresh parameters set in the request address by clicking the refresh button. (The refreshed parameters will override the original parameter settings.)
Header	The header parameter is placed in the request header. Commonly used constant values are usually passed through the header because a request must have a header. The request header parameter only supports manual addition, deletion, and modification. Formulas support all formulas except report functions and cell calculations.
Encoding	If the file is not encoded in UTF-8, select the corresponding encoding in Encoding, otherwise, the text information cannot be parsed correctly.

Note:

1. In version 9.3.3 of the plugin, the script engine supports Java (default) and JavaScript V8. On machines that do not support JavaScript V8, Java is automatically used.

2. The addition of parameter types depends on what type of parameters your server needs to receive.

Fetching JSON Dataset Data

Creates a JSON dataset in the designer, as shown in the following figure.

1. Fetching All Data

Right-click the created JSON dataset, select Edit, and enter the query statement to fetch the data from the JSON dataset. Enter the query statement $.store.book[*] to query all book information in the bookstore, as shown in the following figure.

Note:

$.store.book[*] is a fixed reference in JSON, which means it fetches all data of book in store.

Click Preview to view the fetched JSON data.

Key Sort refers to sorting the fetched column names, which is not enabled by default. (If the JSON structure is incomplete, enable Column Name Prefetch at the same time to make Key Sort effective.)

Column Name Prefetch refers to traversing all the data and fetching the column names, which can be used for situations where the JSON structure is not uniform. However, Column Name Prefetch will slightly reduce efficiency. Similar to Key Sort, Column Name Prefetch is disabled by default.

2. Fetching a Specific Column

Enter the query statement $.store.book[*].category to fetch the data in the category column.

Click Preview to view the fetched JSON data.

3. Filtering and Fetching Data

Enter the query statement $.store.book[?(@.price<10)] to fetch books with a price less than 10.

Click Preview to view the fetched JSON data.

Note:

1. Inconsistent key-value pair order may cause data corruption, you can enable Column Name Prefetch to solve this problem.

2. You can download the completed template JSON Dataset Example1.cpt at the end of the document.

Parameter Passing Method

Difference in Parameter Passing Methods

Note:

1. You are advised to use a simple interface link when fetching parameters because the .json file cannot pass parameters.

2. In the dashboard, if you need a linkage between the JSON data connection and control, you must add parameters with the same name as the JSON data connection to the JSON dataset (even if this parameter may not be used).

3. When the parameter in JSON datasets is passed, the parameter value must be a fixed value and cannot be another parameter, for example, fine_username.

1. GET Parameter Passing

Create a JSON data connection, select the request type as GET, and suffix the request address of the JSON text with the parameter, as shown in the following figure.

Add a JSON dataset, enter the query statement, and the data corresponding to the parameter book is returned, as shown in the following figure.

2. POST_FORM Parameter Passing

Create a JSON data connection and select POST_FORM as the request type. Enter the corresponding JSON text interface link in URL and manually add common parameters.

Add a JSON dataset, enter the query statement, and the data corresponding to the parameter book is returned, as shown in the following figure.

3. POST_RAW Parameters Passing

Because you need to manually specify parameters with POST_RAW passing parameters, set Content-Type to application/json in Request Header, indicating that the client informs the server that the sent data type is JSON format.

Create a JSON data connection and select the request type as POST_RAW. Enter the corresponding JSON text interface link in URL.

When you preview, the data corresponding to the parameter book is returned, as shown in the following figure.

When you use dynamic parameters, the parameters are passed with ${}. Click the refresh button to configure parameters.

This dataset can convert JSON data to a two-dimensional table with JavaScript. This method can confront all scenarios where JSON datasets cannot be directly used, and you can customize scripts based on your data structures to achieve connection with reports.

Note:

To create and use JSON program datasets, the version of the designer needs to be 2019-03-13 onwards, and the plugin needs to be 9.2.3 onwards.

Creating a JSON Program Dataset

Create a JSON program dataset in the designer, as shown in the following figure.

Fetching All Data

Right-click the created JSON dataset, select Edit, and enter the script code to fetch data of all books.

The script code is shown as follows.

var books = $.store.book;
var rowCount = books.length;
console.log("Row Number: " + 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))

Click Preview to view the fetched JSON data.

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

Filtering Data

Enter the following script code to fetch book data with a price of 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));

Click Preview to view the fetched JSON data.

Fetching a Specific Column

Enter the following script code to fetch the collection of authors for 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);

Click Preview to view the fetched JSON data.

Note:

You can download the completed template JSON Dataset Example2.cpt at the end of the document.

JSON Dataset Example 1.cpt.

JSON Dataset Example 2.cpt.

Note:

The effects on PC and mobile terminals are the same. You can choose a dataset as needed.

You can download the example template.

JSON Dataset Example1.cpt

JSON Dataset Example2.cpt