esProc SPL is a powerful, open-source computing engine that can handle JSON-related operations conveniently.

Download, installation and integration

Find source code of esProc SPL in github.com/SPLWare/esProc. As it takes time and effort to compile the source code, a ready-to-use installation package is offered by the vendor for users to download. You can also Google “esProc SPL” and find official website to download the installation package if necessary.

After esProc SPL is installed, three jar files for integrating it into Java are in place under [installation directory]\esProc\lib:

esproc-bin-xxxx.jar  The driver package of esProc SPL and its JDBC
icu4j_60.3.jar       Handle internationalization
jdom-1.1.3.jar       Parse configuration file

Load these jars to your Java project and you can execute SPL code through JDBC driver:

Class.forName("com.esproc.jdbc.InternalDriver");
Connection connection =DriverManager.getConnection("jdbc:esproc:local://");
Statement statement = connection.createStatement();
ResultSet result = statement.executeQuery("=1");
result.next();
int IntResult=results.getInt(1);

The integration is successful if the above code is correctly executed and we can extract 1 from IntResult.

Basic JSON processing

Now we can handle JSON data using SPL.

Let’s begin with a simple case. strJson is a variable containing the JSON string in Java. Below is part of content of the variable:

[
	{"OrderID":32,"Client":"JFS","SellerId":3,"Amount":468.0,"OrderDate":"2009-08-13"}
	{"OrderID": 70,"Client": "DSG","SellerId": 7,"Amount": 288,"OrderDate": "2009-09-30"}, 
	{"OrderID": 131,"Client": "FOL","SellerId": 7,"Amount": 103.2,"OrderDate": "2009-12-10"},
	...    
]

Java passes the string to SPL and the latter performs grouping and aggregation on it:

PreparedStatement pstmt = connection.prepareStatement("=json(?).groups(Client;sum(Amount):amt, count(1):cnt)");
pstmt.setString(1,strJson);
ResultSet result =pstmt.executeQuery();

We pass a parameter to the SPL statement using the question mark (?) as we do in invoking a database SQL statement. In the returned result, we can view the following result:

Client	amt	cnt	
ARO	899.0	1	
BDR	4278.8	4	
BON	2564.4	1	
…

Here the returned ResultSet is a table. The returned result can also be a JSON string:

PreparedStatement pstmt = connection.prepareStatement("=json(json(?).groups(Client;sum(Amount):amt, count(1):cnt))");
pstmt.setString(1,strJson);
ResultSet result  =pstmt.executeQuery();
result.next();
String strResult=results.getString(1);

Computing result:

[{“Client”:“HDR”,“amt”:21040.0,“cnt”:21},{“Client”:“IBM”,“amt”:19030,“cnt”:19},…]

It is simpler to handle JSON data coming from a file. Use SPL code to read a JSON file containing same content as the previous, and group and summarize the data:

statement.executeQuery("=json(file(\"d:/orders.json\").read()).groups(Client;sum(Amount):amt, count(1):cnt)");

The code returns same result.

SPL supports a wealth of string functions and date functions. For instance, we are trying to find orders in the year 2021 where customer names contain the specified string (only SPL code is provided in this and following cases):

=json(file(\"d:/orders.json\").read()).conj(Orders).select(year(OrderDate)==2012 && like@c(Client,"*business*"))

Find more SPL functions and syntax in esProc SPL Documentation and accomplish most regular computations in a single statement.

Advanced JSON processing

Multilevel JSON handling

Multilevel JSON data is common. Here is one block of JSON, where the upper level contains employee records and the lower level contains employees’ orders records:

[{
    "EId": 7,"State": "Illinois","Dept": "Sales","Name": "Alexis","Gender": "F","Salary": 9000,"Birthday": "1972-08-16",
    "Orders": [
	{"OrderID": 70,"Client": "DSG","SellerId": 7,"Amount": 288,"OrderDate": "2009-09-30"}, 
	{"OrderID": 131,"Client": "FOL","SellerId": 7,"Amount": 103.2,"OrderDate": "2009-12-10"}
    ]
}
{
"EId": 8,"State": "California",
 ...
}]

Computing target: Java passes the JSON string to SPL, and the latter parses the string, concatenate records on the lower level and perform filtering. More than several steps are needed to accomplish the computation, during which specialized SPL development and debug environment are required. Execute [installation directory]\esProc\bin\esproc.exe (for Linux\Mac, it is esproc.sh), open SPL IDE and edit the following script file:

	A
1	=json(strJson)
2	=A1.conj(Orders)
3	=A2.select(Amount>arg1 && Amount<=arg2 && like@c(Client,"*business*"))

Below is the SPL script in SPL IDE:

In the SPL code, strJson, arg1 and arg2 are all parameters. They can come from Java JDBC or the reporting tool. Define the parameters through Program->Parameter on menu bar:

Note: In the SPL code, the JSON string is a constant that is ready to use. But in the Java code, the JSON string is an ordinary string. An ordinary string needs to be parsed by json() function before it can be used. In order to maintain code consistency, the JSON string parameter should be preceded by a single quotation mark when being tested in SPL IDE to simulate an ordinary string.
After executing or debugging the SPL script, click A1-A3 in order to view the result of each step:

Now we invoke the SPL script in the Java. First save the SPL script as a file, like splFile.splx, and reference the file name in a Java program as invoking a stored procedure.

Class.forName("com.esproc.jdbc.InternalDriver");
Connection connection =DriverManager.getConnection("jdbc:esproc:local://");
CallableStatement statement = connection.prepareCall("{call splFile (?,?, ?)}");
statement.setString(1,strJson);
statement.setInt(2, 1000);
statement.setInt(3, 3000);
ResultSet result= statement.executeQuery();

Often a JSON string comes from an HTTP data source (such as Restful) instead of a file. SPL can directly read data from an HTTP source without inputting a parameter from Java. For example:

	A
1	=httpfile("http://127.0.0.1:6868/restful/emp_orders").read()
2	=json(A1)
3	=A2.conj(Orders)
4	=A3.select(Amount>1000 && Amount<=3000 && like@c(Client,"*business*"))

Find more detail issues (like access security) in SPL: Access to HTTP/WebService/Restful Service.

SPL offers external library interfaces to support a variety of data sources. You can retrieve and compute data from Kafka directly, for instance:

	A
1	=kafka_open("/kafka/my.properties", "topic1")
2	=kafka_poll(A1)
3	=A2.derive(json(value):v).new(key, v.fruit, v.weight)
4	=stax_close(A1)

SPL also supports MongoDB bson format:

	A
1	=mongo_open("mongodb://127.0.0.1:27017/mongo")
2	=mongo_shell(A1,"test1.find()")
3	=A2.conj(Orders)
4	=A3.select(Amount>1000 && Amount<=3000 && like@c(Client,"*business*")).fetch()
5	=mongo_close(A1)

You need to make configurations to each external library before you can use them. Find related information in External Library Guide.

Extended reading includes From JSONPath and XPath to SPL and Open-source SPL: The Sharp Tool for Processing Retrieved Webservice/Restful Data.