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 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 Java parameters. Here’s the result:
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:
You need to export the file in Java as a CSV file, whose desired format is as follows:
esProc script:
|
1 |
=file(“d:\\data.json”).read().import@j() |
|
2 |
=A1.People |
|
3 |
=file(“D:\\result.csv”).export(A2;”,”) |
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:
Exporting a multi-level JSON file as a CSV file
Below is the original JSON file:
You want to arrange the data in two columns in the CSV file， as shown below:
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:
|
1 |
=file(“d:\\source.json”).read().import@j() |
|
2 |
=A1.fno().(pjoin([f=A1.field(~)].fname():key,f.array():value)) |
|
3 |
=A2.(~.record@i([A1.fname(#),null],1)) |
|
4 |
=A3.conj() |
|
5 |
=file(“D:\\result.csv”).export(A4;”,”) |
Import the JSON file and divide data into 3 groups according to the root fields, as shown below (A2):
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:
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 script:
|
|
A |
B |
C |
D |
|
|
1 |
=file(“E:\\s.json”).read().import@j() | ||||
|
2 |
=create(Groupname,groupid) | ||||
|
3 |
=create(Serviceid,Servicename,groupid,Credit,Time,INFO,Network,Mobile,Provider, PIN,KBH,MEP,PRD,Type,Locks,Reference) |
||||
|
4 |
for A1.SUCCESS | =A4.LIST | |||
|
5 |
for B4.fno() | =B4.field(B5) | |||
|
6 |
=C5.SERVICES | ||||
|
7 |
for C6.fno() | =C6.fname(C7) | |||
|
8 |
=C6.field(C7) | ||||
|
9 |
=A2.record([C5.GROUPNAME,D7]) | ||||
|
10 |
=A3.record([D8.#1,D8.#2,D7,D8.#3,D8.#4,D8.#5, D8.#6,D8.#7,D8.#8,D8.#9,D8.#10, D8.#11,D8.#12,D8.#13,D8.#14,D8.#15]) |
||||
|
11 |
=mssql.update(A2,groups,Groupname,groupid;groupid) | ||||
|
12 |
=mssql.update(A3,services,Serviceid,Servicename,groupid,Credit,Time,INFO,Network,Mobile,Provider, PIN,KBH,MEP,PRD,Type,Locks,Reference;Serviceid) |
||||
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:
