. Use JSON as a Data Set in BIRT Report

 

BIRT has no build-in JSON data source. However, there are some community JSON data source plugins, but all of them I have seen are very low level and not comfortable to use.

There are two ways to use JSON as a Data Set in Birt.

1). Use scripted datasource to deal with simple JSON format.

Here’s the sample data:

{
    "bindings": [
        {
            "name": "Bob",
            "title": "Manager",
            "phone": "555-000-0012"
        },
        {
            "name": "Tom",
            "title": "Sales Rep",
            "phone": "555-000-0037"
        },
        {
            "name": "Larry",
            "title": "Tech",
            "phone": "555-000-0021"
        }
    ]
}

Here is Javascript program for scripted datasource.

Create a scripted datasource with open() method as follows:

importPackage(Packages.org.apache.commons.io);
importPackage(Packages.java.io);

// Grab the JSON file and place it in a string
fisTargetFile =new FileInputStream(new File("C:/Users/kclark/Desktop/test.json"));
input =IOUtils.toString(fisTargetFile,"UTF-8");// Store the contents in a variable
jsonData = input;

// Convert the String to a JSON object
myJSONObject =eval('('+ jsonData +')');

Then create a dataset with the following methods:
open()

count =0;// Counter used to step through each item in the JSON object.

fetch()

// Loop through the JSON object adding it to the scripted data source
// Get the length of the object
len = myJSONObject.bindings.length;
if (count >= len)
    return false;
  
row["name"]= myJSONObject.bindings[count].name;
row["title"]= myJSONObject.bindings[count].title;
row["phone"]= myJSONObject.bindings[count].phone;

count++;
return true;

You may need Apache Commons IO included in your scriptlib folder.

2). Use esProc datasource to deal with nested JSON.

Here’s the sample data:

[
    {
        "id": 1000,
        "content": "It is too hot",
        "comment": [
            {
                "author": "joe",
                "score": 3,
                "comment": "just so so!"
            },
            {
                "author": "jimmy",
                "score": 5,
                "comment": "cool! good!"
            }
        ]
    },
    {
        "id": 1001,
        "content": "It is too cold",
        "comment": [
            {
                "author": "james",
                "score": 1,
                "comment": "yes!"
            },
            {
                "author": "jimmy",
                "score": 5,
                "comment": "cool!"
            }
        ]
    },
    {
        "id": 1002,
        "content": "It is windy day today",
        "comment": [
            {
                "author": "tom",
                "score": 3,
                "comment": "I do not thinkso!"
            },
            {
                "author": "jimmy",
                "score": 5,
                "comment": "cool!"
            }
        ]
    }
]

Here is the esproc SPL script:


A

1

=file("D:/files/work/txt/blogs.json").read().import@j()

2

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

A1:

Get the resulting table, and you can see that comment field is a field referencing table, as shown below:

Double-click the blue value in the first row to see the referenced table in detail:

A2:

Here, macros are used as arguments to the news function. The macro encloses the expression with $ {}. When SPL calculates, the macro expression is evaluated first, and then the calculation result is used as a string value to replace $ {}.

imagepng

The report can be designed in the same way as you would if you were retrieving the data from a database. For detail SPL integration with BIRT, see How to Call an SPL Script in BIRT.

For more examples, procedure JSON files refer to the following JSON data calculation and importing into database .

The sample data can be downloaded JsonSample. If you have any questions or comments, please leave them below.