Some class libraries, such as sf.json\Gson\JsonPath, can parse JSON, but most of them have done little to further it. A few can handle the simplest conditional queries. Hardcoding is the only choice for handling almost all common computations if users choose to use them. Embedded databases have more computing abilities. Their complicated loading process, however, makes them only suited to tasks having very low real-time requirements. The power of SQL, designed based on two-dimensional structured data rather than intended for the multilevel JSON, is bound to be greatly reduced if it is used to compute JSON.

There is the open-source esProc SPL. It is convenient for parsing JSON, is multilevel structured-oriented for significantly simplifying JSON handling processes, and has agile syntax and rich functions to enable outstanding computational capabilities.

SPL offers the JDBC driver that is convenient to invoke, even for beginners. For instance, we have a two-level JSON file where the first level stores employee records; each record contains an Orders field whose value is a set of orders records, and we are trying to parse the file into a table sequence (which is the SPL multilevel data object):

…
Class.forName("com.esproc.jdbc.InternalDriver");
Connection connection =DriverManager.getConnection("jdbc:esproc:local://");
Statement statement = connection.createStatement();
String str="=json(file("d:/data.json").read())";
ResultSet result = statement.executeQuery(str);
…

You can see that SPL parses the JSON file directly without loading it to the database. It can also retrieve and parse RESTful data directly using simple code:

=json(httpfile("http://127.0.0.1:6868/api/getData").read())

If JSON data is stored in special sources like MongoDB and Elasticsearch, SPL can still retrieve and parse it directly.

We can compute multilevel JSON easily through an SPL table sequence using simple and easy-to-understand code. For instance, we are trying to perform a conditional query on all employees’ all orders in order to find orders records where amounts fall within a specified range and client names contain a specified string. SPL has the following code to do this:

=json(file("d:/data.json").read()).conj(Orders).select((Amount>1000 && Amount<=2000) && like@c(Client,"\*business\*"))

SPL supports saving an algorithm separately and independently to considerably reduce coupling. This is particularly suited to tasks requiring long pieces of code or for which the code will probably undergo frequent changes. So, the above code for achieving conditional query can be first saved as a script file:

Then we can invoke the script file in JDBC as we call a stored procedure:

…
Class.forName("com.esproc.jdbc.InternalDriver");
Connection connection =DriverManager.getConnection("jdbc:esproc:local://");
Statement statement = connection.createStatement();
ResultSet result = statement.executeQuery("call getQuery()");
…

SPL has a wealth of built-in library functions to provide equal computing abilities as SQL. Here are some examples:

Thanks to its rich functions and agile syntax, SPL can deal with algorithms with complex logic with rather simple code that is hard to achieve using SQL or stored procedures. Suppose we have a JSON file that stores client names and their order amounts and are trying to find the first n big clients whose amounts take up at least half of the total and sort them by the amount in descending order.

SPL designs a professional IDE equipped with a complete set of debugging functionalities and letting users observe the result of each step, making it suitable for achieving algorithms with complex logic:

An SPL table sequence can express multilevel JSON in a direct way without the intermediate two-dimensional records SQL uses. So, it is particularly effective for simplifying multilevel JSON handling processes. Below, for instance, is part of a JSON file containing multilevel subdocuments and multilayer sets (arrays)：

[
     {
        "race": {
              "raceId":"1.33.1141109.2",
              "meetingId":"1.33.1141109"
        },
        ...
        "numberOfRunners": 2,
        "runners": [
          {       "horseId":"1.00387464",
                  "trainer": {
                      "trainerId":"1.00034060"
                  },
              "ownerColours":"Maroon,pink,dark blue."
            },
            {     "horseId":"1.00373620",
                  "trainer": {
                      "trainerId":"1.00010997"
                  },
              "ownerColours":"Black,Maroon,green,pink."
            }
        ]
     },
  ...
  ]

To group the file by trainerId and count members of ownerColours in each group, SPL has the following code:

With a lightweight framework, low costs of learning, convenient parsing abilities, rich data source support, and outstanding computational capabilities, SPL can compute multilevel JSON and achieve algorithms with complex logic conveniently in Java.