. How to import JSON data from REST API into BIRT

 

KeywordsJson data source       Rest API data source

JSON data sources cannot be built in BIRT. Although some open source communities provide plug-ins for parsing JSON data sources, almost all plug-ins are very low-level and inconvenient to use, so they are not recommended.

The common way of BIRT is to create a "scripted data source", connect the scripted source to the URL and parse the result by itself, but the script is very cumbersome to write and needs a lot of work.

For example, to deal with such a scenario: Call the REST API according to the incoming parameters and return the nested JSON data. Some data formats are as follows:

[

    {

        "id":   1000,

        "content":   "It is too hot",

        "comment":   [

            {

                "author":   "joe",

                "score":   3,

                "comment":   "just so so!"

            },

            {

                "author":   "jimmy",

                "score":   5,

                "comment":   "cool! good!"

            }

        ]

}

...

]

Expected effect after parsing:

undefined

BIRT uses the open() method to create a scripted data source, as follows:

...

importPackage(Packages.java.io);

importPackage(Packages.java.net);

var param= params["industryname"].value;

var inStream =   new URL("http://yourapi/endpoint/" + param).openStream();

var inStreamReader =   new InputStreamReader(inStream);

var bufferedReader =   new BufferedReader(inStreamReader);

var line;

var result = "";

while ((line   = bufferedReader.readLine()) != null)

result += line;

inStream.close();

var json   = JSON.parse(result);

vars["HTMLJSON"] =   json;

logger.warning (result);

...

It is recommended to use esProc, which encapsulates the return result of the URL string of HTTP service into a file stream, and can directly parse the JSON format string and structure it into a sequence table. esProc has designed a set of function library in the field of set operations to deal with all kinds of structured operations, such as grouping, sorting, filtering, aggregation, join, etc., without the help of a third-party database. For example, in the above problem, the script of esProc only needs three lines:


A

1

=httpfile("http://yourapi/endpoint/servlet/testServlet?table=blog&type=json")

2

=json(file(A1).read())

3

=A2.news(comment;id,content,${A2.comment.fname().concat@c()})

Finally, the calculation results are returned to the dataset of BIRT for report presentation. From this example we can see, compared with the conventional method, the implementation of the same algorithm requires less code, and step-by-step debugging is also convenient.

In fact, there are many similar calculation problems that are not convenient, but it is very simple with the help of esProc SPL. You can refer to:  JSON data calculation and importing into database

esProc provides JDBC driver, and can be easily integrated with BIRT and other reporting tools. Please refer to How to Call an SPL Script in BIRT.

For esProc installation, free authorization and related technical information, please refer to Getting started with esProc