How esProc Assists Java in Handling JSON Data

Java can handle simple parsing of semi-structured JSON data, but has difficulty in handling the in-depth processing. esProc, which supports set operations, order-related operations and dynamic script execution, can help reduce the difficulty. A Java application 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. For more details, see How to Use esProc as the Class Library for Java.

Below are examples of the most common problems you may encounter in handling JSON data in Java, 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_java_json_1

esProc script:

esProc_java_json_2

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

esProc_java_json_3

Parsing JSON files with different fields in each document

The documents of Data.json have different fields whose orders are different too, as shown below:

esProc_java_json_4

You need to export the file in Java as a CSV file, whose desired format is as follows:

esProc_java_json_5

esProc script:

esProc_java_json_6

Import the JSON file into memory and retrieve the People field, and write it into a file separated with commas. Here’s result of A2:

esProc_java_json_7

Exporting a multi-level JSON file as a CSV file

Below is the original JSON file:

esProc_java_json_8

You want to arrange the data in two columns in the CSV file, as shown below:

esProc_java_json_9

The difficulty is to join each of the root fields with its subfields whose names and field values need to be placed into two columns. Below is the esProc script:

esProc_java_json_10

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

esProc_java_json_11

Then insert the record [root field name,null] before the first record of each group and concatenate the groups and export the data as a CSV file, as shown below:

esProc_java_json_12

Dynamically storing a JSON file in the database

s.json contains multi-level subdocuments, among which LIST and SERVICES have different numbers of fields. You need to export s.json to the database tables groups and service in Java. The requirement is that GROUPNAME values and field names of SERVICES will constitute the database table groups and the subfields of SERVICES will form another database table service; the two tables are related through groupsid field.

Below is the source data:

esProc_java_json_13

esProc script:

esProc_java_json_14

Import the JSON file in its original structure, loop through every subdocument of SUCCESS (B4-D10), during which every field of List(C5-D10) and Service (D7-D10) will be accessed, and append records to the empty two-dimensional tables in A2 and A3 and finally perform database updates. The script uses several functions to access a two-dimensional table. The fno function gets the number of fields, the fname function gets field names and the field function gets field values by sequence numbers.

Below are the results of A2 and A3 after data appending:

esProc_java_json_15

Advertisements

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 Java Assistant. Bookmark the permalink.

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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