Report Building Challenges: In-depth Processing of JSON Data

For processing semi-structured JSON data, both Java and the reporting tools provide class libraries that are only capable of simple parsing jobs, giving you no choice but to write complicated code when trying to process the data in-depthly. However, using esProc to prepare the data needed for report building can help you out. The code is quite simple. A reporting tool will execute an esProc script in the same way as it executes a database stored procedure, pass parameters to the script and get the returned esProc result through JDBC. See How to use esProc to Assist Reporting Tools for details.

Below are examples of a range of most common problems you may encounter in presenting JSON data with reporting tools, and their esProc solutions.

Grouping and aggregating JSON data

order.json stores order records. You need to calculate the total sales amount a client contributes per month in a specified time period. Below is a selection of the source data:


esProc script:


Import the JSON file as a two-dimensional table; perform a conditional query and then the group and aggregate. Both argBegin and argEnd are report parameters. Here’s the result:


Presenting cell phone logs in a list table

cellphone.log is a file storing cell phone logs in JSON format. In it, general and ping fields are subdocuments containing one record each, and cap_main field, a subdocument too, contains multiple records. You need to present cap_main field in a list table, with each record being appended by general’s mtmodel and networktype fields and ping’s ssid field.

Below is the source data:


esProc script:


After esProc imports the JSON file, it will retain the file’s multilayer structure. At the same time, it can use periods to access fields in different layers of data. The derive function will add computed columns to the table. Here’s the result:


Querying the multi-level subdocument in a JSON file

jsonstr.json has a subdocument, runners field, which has three fields – horseId, ownerColours and trainer which contains a subfield – trainerId. The report needs to present the horseId, ownerColours and trainerId fields in each subdocument filed according to serial numbers of the documents.

Below is the source data:


esProc script:


Read in the JSON file; retrieve runners field (the subdocument) according to the serial numbers of documents; and get the desired fields, in which trainerId comes from a subdocument of runners. A3 is the data the report needs:


The grouped report from a multi-level JSON file

Cells.json is a multi-level nested JSON file, which you want to present in a grouped report. The grouping fields are name, type and image.”xlink:href”. There is also a field with 3 subdocuments: custom. Identifier, custom. Classifier and custom. Output, which are of the same structure but contain different number of documents each.

Below is the source data:


esProc script:


esProc merges the three subdocuments into a single two-dimensional table, gives them a new field name ctype to be identified and appends the grouping fields to the tabled. Thus a typical “table with subtables” is created.


Now it’s easy to build a grouped report based on this esProc result.

A report with subreports using different JSON files

You might want to create a report containing multiple subreports, where the main report and the subreports use different JSON files as their sources. Below is a selection of the source data


A reporting tool supporting only a single data source, such as Jasper and BIRT, would need to combine the multiple sources into one using JAVA classes, while esProc would use a simple script as follows:


Read in the JSON file and get the first field, which is represented by “.#1”. By assigning different file names to the parameter argFileName, the report will receive different data sets, as the following shows:


Joining a txt file and a JSON file

structure.txt is a structured text file separated by tabs. json.txt contains unstructured JSON strings. There is a foreign key relationship between the second field of structure.txt and part of the json.txt. Below are selections from them:





You need to build a report to present the above relationship, with a layout like this:


esProc script:


Read in the JSON file, retrieve the desired fields and subdocument to create a two-dimensional table and append a computed column to it. Below is A3’s result:


Then import the text file, perform a join between it and A3 and retrieve the desired fields to generate the final result A6, as shown below:



About datathinker

a technical consultant on Database performance optimization, Database storage expansion, Off-database computation. personal blog at: datakeywrod, website: raqsoft
This entry was posted in Reporting tool and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s