esProc Helps Process Heterogeneous Data Sources in Java - JSON

Java’s JSON open source package can only parse JSON data and hasn’t the computational function. It is troublesome for programmers to develop a general program for performing computations, such as grouping, sorting, filtering and joining, by themselves. For example, during developing program for performing conditional filtering in JSON files using Java, the code has to be modified if the conditional expression is changed. If they want to make it as flexible as SQL in performing conditional filtering, they have to write code for analyzing and evaluating expressions dynamically. This requires quite a lot of programming work.

esProc supports dynamic expressions. It also can be embedded in Java to write the general program for computing JSON data. Let’s give an example to see how it works. There are to-be-processed JSON strings that contain employee information, including fields such as EID, NAME, SURNAME, GENDER, STATE, BIRTHDAY, HIREDATE and DEPT, etc. Parse the strings and select female employees who were born on and after January 1st, 1981. The content of the strings is shown as follows:

[{EID:1,NAME:"Rebecca",SURNAME:"Moore",GENDER:"F",STATE:"California",BIRTHDAY:1974-11-20,HIREDATE:2005-03-11,DEPT:"R&D",SALARY:7000},

{EID:2,NAME:"Ashley",SURNAME:"Wilson",GENDER:"F",STATE:"New York",BIRTHDAY:1980-07-19,HIREDATE:2008-03-16,DEPT:"Finance",SALARY:11000},

{EID:3,NAME:"Rachel",SURNAME:"Johnson",GENDER:"F",STATE:"New Mexico",BIRTHDAY:1970-12-17,HIREDATE:2010-12-01,DEPT:"Sales",SALARY:9000},…]

Implementation approach: Call esProc program using Java and input the JSON strings which will then be parsed by esProc, perform the conditional filtering and return the result in JSON format to Java. Because esProc supports parsing and evaluatingexpression dynamically, it enables Java to filter JSON data as flexibly as SQL does.

“where” is a string, its values isBIRTHDAY>=date(1981,1,1) && GENDER=="F".

A1：Parse the JSON data into a table sequence. esProc’s IDE can display the imported data visually, as shown in the right part of the above figure.

A2: Perform the conditional filtering, using macro to realize parsing the expression dynamically. The “where” in this process is an input parameter. In executing, esProc will first compute the expression surrounded by ${…}, take the computed result as macro string value, replace ${…} with it and then interpret and execute the code. The final code to be executed in this example is =A1.select(BIRTHDAY>=date(1981,1,1) && GENDER=="F").

A3: Generate JSON strings using the filtered table sequence.

A4：Return the eligible result set to the external program.

Since the esProc script is called in Java through the JDBC interface, the returned result is set- the object of ResultSet. Fetch the first field of string type in set, and this is the filtered JSON string. Detailed code is as follows (save the above program in esProc as test.dfx):

// create a connection

Class.forName("com.esproc.jdbc.InternalDriver");

con= DriverManager.getConnection("jdbc:esproc:local://");

// call the program in esProc (the stored procedure); test is the name of file dfx

com.esproc.jdbc.InternalCStatementst;

st =(com. esproc.jdbc.InternalCStatement)con.prepareCall("call json(?,?)");

// set the parameters; as the JSON string is long, part of it is omitted.

//In practice, JSON strings may be generated by various ways; see below for the explanation.

String jsonstr=

"[{EID:1,NAME:\"Rebecca\",SURNAME:\"Moore\",GENDER:\"F\",STATE:\"California\...}]";

st.setObject(1,jsonstr);

st.setObject(2,"BIRTHDAY>=date(1981,1,1) && GENDER==\"F\"");

// execute the esProcstored procedure

ResultSet set=st.executeQuery();

// get the filtered JSON string

String jsonstrResult;

if(set.next()) jsonstrResult = set.getString(1);

JSON format is common used by interactive data in internet application. In practice, JSON strings may be retrievedfrom local files or remote HTTP server. esProc can directly read JSON strings from the files or the HTTP server. Take the latter as an example. It is assumed that there is a testServlet which returns JSON strings of employee information. The code for performing the operation is as follows:

A1：Define an httpfile object, the URL is

http://localhost:6080/myweb/servlet/testServlet?table=employee&type=json.

A2：Read the result returned by the httpfile object.

A3：Parse the JSON string and generate a table sequence.

A4：Filter data according to the conditions.

A5：Convert the filtered table sequence to JSON strings.