With class library for merely simple parsing jobs, Java and reporting tools have difficulty in handling in-depth processing of semi-structured JSON data. esProc can help reduce the difficulty. 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:
|
|
A |
|
1 |
=file(“D:\\order.json”).read().import@j() |
|
2 |
=A1.select(OrderDate>=argBegin && OrderDate<=argEnd) |
|
3 |
=A2.groups(month(OrderDate):Month,Client;sum(Amount):subtotal) |
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:
|
|
A |
|
1 |
=file(“D:\\ cellphone.log”).read().import@j() |
|
2 |
=A1.cap_main.derive(A1.general.mtmodel,A1.general.networktype,A1.ping.ssid) |
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:
|
1 |
=file(“D: \\jsonstr.json”).read().import@j() |
|
2 |
=A1(which).runners |
|
3 |
=A2.new(horseId,trainer.trainerId:trainerId,ownerColours) |
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:
|
|
A |
|
1 |
=file(“D:\\cells.json”).read().import@j() |
|
2 |
=A1.cells.conj(custom.(identifier.new(name:cname,URI:cURI,”identifiler”:cType,A1.cells.name:name,A1.cells.type:type,A1.cells.image. ‘xlink:href’:image)|classifier.new(name:cname,URI:cURI,”classifier”:cType,A1.cells.name:name,A1.cells.type:type, A1.cells.image.’xlink:href’:image)|output.new(name:cname,URI:cURI,”output”:cType,A1.cells.name:name, A1.cells.type:type,A1.cells.image.’xlink:href’:image))) |
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:
|
|
A |
|
1 |
=file(“D:\\”+argFileName).read().import@j().#1 |
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:
structure.txt
Json.txt
You need to build a report to present the above relationship, with a layout like this:
esProc script:
|
|
A |
|
1 |
=file(“D: \\json.txt”).read().import@j() |
|
2 |
=A1.new(#1.name:name,#1.member.(#1):cluster) |
|
3 |
=A2.derive(left(cluster,(firstblank=pos(cluster,” “)-1)):key,right(cluster,len(cluster)-firstblank):value) |
|
4 |
=file(“D:\\ structure.txt”).import() |
|
5 |
=join(A4,_2;A3,key) |
|
6 |
=A5.new(_1._1,_1._2,_1._3,_1._4,_1._5,_1._6,_1._7,_1._8,_2.name,_2.value) |
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:
