Examples of Handling JSON Data with esProc

JSON format multilevel semi-structured data is commonly seen in internet applications. Java provides just the class library for parsing JSON data, but to perform in-depth calculations, complex hardcoding is required.

esProc supports set-operations, order-related calculations and dynamic script execution, so it can be used as the class library for Java to make JSON data manipulation much easier. The Java application can access the esProc result through JDBC. For details, see How to Use esProc as the Class Library for Java.

Following is the typical JSON data manipulation cases, and their esProc solutions.

Grouping and aggregating JSON data

order.JSON contains the ordering records. The requirement is to calculate the sales amount that each client contributes per month in a specified time period. Below is a selection of the source data:

The code:

Import the JSON file as a two-dimensional table and perform a conditional query and then the grouping and aggregation. argBegin and argEnd are parameters. Here’s the result:

Handling JSON format HTTP flows

testServlet returns JSON format strings of employee information. Now query the data according to the given condition and return result to Java main program in JSON format.

Read in the http file object and parse the JSON format strings to generate a two-dimensional table; and then filter the table according to the condition and export the result as JSON format strings. The parameter where is the dynamic querying condition, like BIRTHDAY>=date(1981,1,1) && GENDER==”F”.

Converting cell phone logs into structured data

The cellphone.log file contains the JSON format cell phone log, in which general field and ping field are subdocuments that each has one record. Another subdocument cap_main has multiple records. The requirement is to display cap_main in a list table, with each record appended by general’s mtmodel field and networktype field and ping’s ssid field.

Below is the source data:

The code:

esProc can read in the JSON file while keeping its multilevel structure, and then it can use periods to access fields through the multiple levels. The derive function adds the computed columns. Here’s the result:

Querying multilevel JSON subdocuments

JSONstr.JSON’s runners field is a subdocument that has 3 fields: horseId, ownerColours and trainer, in which trainer has a subfield trainerId. The requirement is to query the subdocument’s horseId, ownerColours and trainer fields according to the corresponding document’s sequence number.

Below is the source data:

The code:

Import the JSON file, retrieve the document’s runners field (the subdocument), and get the desired fields from runners. trainerId comes from a child document of the subdocument. A3 gets the result as follows:

Converting multilevel JSON to structured data

Cells.JSON is a multilevel nested JSON file which needs to be converted to a two-dimensional structured table according to the grouping fields name, type and image. “xlink:href”. The field containing detailed data has 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:

The code:

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 table to finally generate a typical “table with subtables”. Below is the concatenation result of A2:

Exporting the multilevel JSON file as CSV file

Below is the original json file:

The expected CSV file will present the source data in two columns, as shown below:

The difficulties are that the root field names should be combined with the subfield names and that the subfield names and values should each be presented in a separate column. Here’s the code:

Read in the JSON file and divide data into 3 groups according to the 3 root documents, as shown below (A2):

Next, insert [root field name,null] into each group as the first record and concatenate all the groups of records and finally export them. The exported CSV file is as follows:

Joining a txt file and a JSON file

structure.txt is a tab-separated structured file. JSON.txt contains unstructured JSON strings. The second field of structure.txt and part of the json.txt are related through the foreign key. The requirement is to join the two files into a two-dimensional table. Below are selections of them.

structure.txt

JSON.txt

The code:

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 as a table, perform a join between the table and A3 and retrieve the desired fields to generate the final result A6, as shown below:

Dynamically exporting JSON data to the database

s.json contains multilevel subdocuments, in which the LIST subdocument and SERVICES subdocument each have a variable number of fields. The requirement is to write s.json into two database tables – groups and service – by filling the values of GROUPNAME and the names of fields under SERVICES in the database table groups, and entering the detailed fields under SERVICES into the database table service. The two database tables are related through groupid.

Below is the source data:

The code:

Read in the JSON file while retaining its original structure; loop through each subdocument of SUCCESS (B-D10), each field of LIST (C5-D10) and each field of SERVICES (D7-D10) to append the desired records to the empty two-dimensional tables A2 and A3 respectively, and then export the tables to the database. In the above code, several functions for accessing the structural elements of a two-dimensional table are used. The fno function finds the number of fields, the fname function gets the field names by serial numbers, and the field function gets the field values by serial numbers.

Below are A2 and A3, with desired records appended