JSON Dataset

1) FineReport can create datasets from JSON files by identifying keys as columns and key values as data values.

2) You can create JSON datasets via query statements or JavaScript.

1.System requirements 

1) Please contact our technical support team (international@fanruan.com) to get the JSON Dataset pulgin. 

Designer plugin installation method reference: Plugin Management

Server installation plugin method reference: Server plugin management

2) Make sure the version of FineReport designer/server, the JAR package and the plugin match.

2.Define data connection

Click + and choose JSON.

3.Sample JSON file

http://fine-doc.oss-cn-shanghai.aliyuncs.com/book.json?

{
    "store": {
        "book": [
            {
                "category": "reference",
                "author": "Nigel Rees",
                "title": "Sayings of the Century",
                "price": 8.95
    },
            {
                "category": "fiction",
                "author": "Evelyn Waugh",
                "title": "Sword of Honour",
                "price": 12.99
    },
            {
                "category": "fiction",
                "author": "Herman Melville",
                "title": "Moby Dick",
                "price": 8.99,
                "isbn": "0-553-21311-3"
    },
            {
                "category": "fiction",
                "author": "J.R.R. Tolkien",
                "title": "The Lord of the Rings",
                "price": 22.99,
                "isbn": "0-395-19323-5"
    }
  ],
        "bicycle": {
            "color": "red",
            "price": 19.95
        }
    }
}

4.Configure the data connection 

Specifications：

5.Create a new JSON table data

6.Sample query-Get all rows and columns

1) Query statement: $.store.book[*] 

2) Click preview and you can see all the data extracted from array [book].

7.Sample query-Get one column

1) Query statement: $.store.book[*].category

2) Preview and all values corresponding to key [category] are extracted as one column.

8.Sample query-Filter data

1) Query statement: $.store.book[?(@.price<10)]

2) Preview and data with the value corresponding to key [price] lower than 10 are extracted.

9.Specifications of the JSON table data

1) Documentations on querying JSON: querying JSON

2) Key Sorted: Disabled by default. When enabled, the extracted column names are sorted.

3) Prepare Keys: Disabled by default. When enabled, the dataset generation begins with traversing the JSON file to identify all keys, and this is suitable when the orders of key-value pairs are inconsistent in the JSON object.   

10.Create a new JSON script table data    

11.Sample script-Get all rows and columns

var books = $.store.book;
var rowCount = books.length;
console.log(rowCount + " Rows");
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("A total of " + row.length + " columns");
    table.push(row);
});
return merge(table, unique(column));

12.Sample script-Get one column

Extract the values corresponding to key [author].

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);

13.Sample script-Filter data 

Extract data with the value corresponding to key [price] smaller 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));

14.Specifications of the script

1.POST_FORM

Create a JSON data connection and select "POST_FORM" as the request type. Enter the corresponding JSON text interface link at the "Url" and manually add common parameters. As shown below:

Add the "JSON" dataset, input the query statement, and return the data corresponding to the parameter book, as shown below:

2.POST_RAW

The POST_RAW parameter needs to be manually specified. Therefore, set "Content-Type" in the request header parameter to "application/JSON", indicating that the client tells the server that the data type to be sent is in JSON format.

Create a JSON data connection and select "POST_FORM" as the request type. Enter the corresponding JSON text interface link at the "Url". As shown below:

Click "Preview", the data corresponding to parameter bookis returned, as shown in the following figure:

When using dynamic parameters, pass dynamic parameters through ${} and click the refresh button to configure parameters: