Use an Embedded Script to Quickly Parse Multilevel JSON Data in Java

 

Key words: Multilevel JSON data  Java  Embedded Script

There are many open source products available for processing JSON data, such as Json-lib, Jackson, Gson and Fastjson. Google Gson is powerful and Alibaba’s Fastjson is efficient.

Parsing JSON data isn’t the goal. What we need is to process the parsed data. Usually those open sources provide a variety of interfaces to read and parse JSON data. But users still need hardcoding to filter, compute and return result as a two-dimensional table to feed data to a third-party service. Generally the relational database is used to do jobs - create temporary tables, parse data and write it in and then compute data in SQL. Getting all this done is nott a piece of cake.

For example, orders data is stored in two-level JSON. The first level is country and region, and the second level contains detailed data. The task is to get orders in North China in 2013.

Below is part of the source data:

[{"COUNTRY":"China","AREA":"Northeast   China","ORDERS":[

{"ORDER_ID":10252,"CUSTOMER_ID":"SUPRD","EMPLOYEE_ID":4,   …},

{"ORDER_ID":10318,"CUSTOMER_ID":"ISLAT","EMPLOYEE_ID":8,   …},

…]},

{"COUNTRY":"China","AREA":"East   China","ORDERS":[

{"ORDER_ID":10249,"CUSTOMER_ID":"TOMSP","EMPLOYEE_ID":6,   …},

{"ORDER_ID":10251,"CUSTOMER_ID":"VICTE","EMPLOYEE_ID":3,   …},

…]},

…]

Expected structuralization and filtering result:

COUNTRY

AREA

ORDER_ID

CUSTOMER_ID

EMPLOYEE_ID

ORDER_DATE

China

North China

10402

ERNSH

8

2013-01-02

China

North China

10403

ERNSH

4

2013-01-03

China

North China

10404

MAGAA

2

2013-01-03

China

North China

10407

OTTIK

2

2013-01-07

Below is part of the Java script for parsing the JSON data:

...

JSONObject jsonObject =   JSONObject.fromObject(orderstr);

JSONArray jsonArray =   jsonObject.getJSONArray("COUNTRY");

...

JSONArray twos =   jsonArray.getJSONArray("ORDERS");

JSONObject two = null;

List<Map<String, String>>   list = new ArrayList<Map<String, String>>();

for   (int i = 0; i < twos.size(); i++) {

two   = twos.getJSONObject(i);

Map<String,   String> map = new HashMap<String, String>();

...

list.add(map);

}

...

The parsing phase would be simple if we could use esProc to handle it. esProc does it with concise code by second encapsulating the JSON class library. It also provides a set-operation-related function library to handle subsequent computations, which makes a third-party database unnecessary. With esProc, a 4-line script is sufficient to get the above JSON data parsed and filtered:


A

1

=json(file("orders.json").read())

2

=A1.select(COUNTRY=="China"   && AREA.contain("North China"))

3

=A2.news(ORDERS;COUNTRY,AREA,${B1.ORDERS.fname().concat@c()})

4

=A3.select(year(ORDER_DATE)==2013)

esProc SPL (Structured Process Language) can conveniently handle many other scenarios involving JSON parsing and computation and writing to the database. Read JSON Processing & Writing to Database to learn more.

esProc is integration-friendly. Read How to Call an SPL Script in Java to see how we can easily embedded an SPL script into a Java program.

Read Getting Started with esProc to download and install esProc, get a license for free and find related documentation.