JSON is a common data-interchange format. JSON data come from external Java programs, local files or the HTTP server. esProc can easily parse the JSON strings and compute them.
The following examples will show in detail how to write an esProc script for doing this.
The first case is the one in which the JSON string is transferred from the external Java program in the form of a parameter.
Suppose there is a string holding employee information in JSON format and including the following fields – EID, NAME, SURNAME, GENDER, STATE, BIRTHDAY, HIREDATE and DEPT. Now parse the string and find out the female employees born after January 1, 1981 inclusive. The string is 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},…]
Solution: Call the esProc script using the Java program and meanwhile input the JSON string; parse and filter the JSON data and then return the result in the form of JSON string to the Java program.
To select female employees born after January 1, 1981 inclusive, esProc script inputs two parameter – “jsonstr” and “where” – from the external program, as shown below：
“where” is a string, its value is BIRTHDAY>=date(1981,1,1) && GENDER==”F”.
esProc script for doing this task:
| A | ||
| 1 | =jsonstr.import@j() | |
| 2 | =A1.select(${where}) | |
| 3 | =export@j(A2) | [{EID:4,NAME:”Emily”,SURNAME:”Smith”,GENDER:”F”,STATE:”Texas”,BIRTHDAY:1985-03-07,HIREDATE:2006-08-15,DEPT:”HR”,SALARY:7000}…] |
| 4 | return A3 |
A1: Parse the JSON data into a table sequence. The result can be displayed in esProc’s Integration Development Environment (IDE), as shown in the right part of the figure above.
A2: Filter the data of the table sequence. A macro is used to dynamically parse the expression, in which “where” is the input parameter. esProc will compute the expression in ${…}, take the computed result as the macro string value and replace ${…} with it, and then parse and execute the expression. The final code for execution is =A1.select(BIRTHDAY>=date(1981,1,1) && GENDER==”F”).
A3: Export the data of the filtered table sequence in the form of a JSON string.
A4: Return the eligible result set to the external program.
Please refer to esProc Tutorial for the method of calling an esProc script in Java program.
Another case is that the JSON data comes from the HTTP server. Suppose to return a JSON string of employee information with a testServlet, then you can get and process the data as follows:
| A | |
| 1 | =httpfile(“http://localhost:6080/myweb/servlet/testServlet?table=employee&type=json”) |
| 2 | =A1.read() |
| 3 | =A2.import@j() |
| 4 | =A3.select(${where}) |
| 5 | =export@j(A4) |
| 6 | return A5 |
A1: Define a httpfile object, 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 the data according to the specified condition.
A5: Convert the filtered table sequence to a JSON string.
A6: Return the result of A5 to the Java program invoking the esProc script.
What’s more, if the JSON data include nested object, esProc can still parse and compute them. For example, there are nested comments in the blog information. esProc can parse them into fields which can be referenced. The blogs.json file is as follows:
[
{ “id” : 1000,
“content” : “It is too hot”,
“comment” :
[
{
“author” : “joe”,
“score” : 3,
“comment” : “just so so!”
},
{
“author” : “jimmy”,
“score” : 5, “comment” : “cool! good!”
}
]
},
{ “id” : 1001,
“content” : “It is too cold”,
“comment” :
[
{
“author” : “james”,
“score” : 1,
“comment” : “yes!” },
{
“author”: “jimmy”,
“score” : 5,
“comment” : “cool!”
}
]
},
{ “id” : 1002,
“content” : “It is windy day today”,
“comment” :
[
{
“author” : “tom”,
“score” : 3,
“comment” : “I do not thinkso!”
},
{
“author” : “jimmy”,
“score” : 5,
“comment” : “cool!”
}
]
}
]
Use the esProc editor to write a script in which the JSON file will be imported, data are parsed and computed:
| A | |
| 1 | =file(“D:/files/work/txt/blogs.json”).read().import@j() |
Get the resulting table sequence and we can see that comment field acts as a referencing field to reference a table sequence, as shown below:
Double-click the blue part on the first row to see the referenced table sequence in detail, as shown below:
esProc can further perform more complicated set operations based on this table sequence with a referencing field.