support
buy cialis buy valium online buy viagra online

How to Use esProc to Handle JSON Data for Report Development

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_report_json_1

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:

esProc_report_json_3

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_report_json_4

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:

esProc_report_json_6

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_report_json_7

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:

esProc_report_json_9

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_report_json_10

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.

esProc_report_json_12

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.

esProc_report_json_13

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:

esProc_report_json_15

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

esProc_report_json_16

Json.txt

esProc_report_json_17

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

esProc_report_json_18

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:

esProc_report_json_20

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:

esProc_report_json_21

replica franck-muller watches  replica hublot watches  replica panerai watches  replica bvlgari watches  replica patek-philippe watches  replica montblanc watches  replica longines watches  replica hermes watches  replica audemars piguet watches  replica vacheron constantin watches