After receiving JSON from RESTful services, further calculations are often required. Processing such data in Java is cumbersome. In contrast, esProc simplifies this significantly, as it can connect to RESTful interfaces, excels at processing multi-layered data, and can be embedded in Java applications as an in-application computing engine.

First, download esProc Standard Edition at https://www.esproc.com/download-esproc/.

After installation, try accessing RESTful data.

Accessing RESTful

Direct access

REST services offer various data access interfaces. For example, accessing http://192.168.2.52:8503/orders can retrieve order data.

The Orders data uses nested JSON to store order, product, and related information, as structured below:

[
  {
    "order_id": "ORD001",
    "order_date": "2025-03-01",
    "customer": "Alice Johnson",
    "order_details": [
      {
        "detail_id": "D001",
        "quantity": 2,
        "price": 50.0,
        "product": {
          "product_id": "P001",
          "name": "Wireless Mouse",
          "category": "Electronics"
        }
      },
      {
        "detail_id": "D002",
        "quantity": 1,
        "price": 120.0,
        "product": {
          "product_id": "P002",
          "name": "Mechanical Keyboard",
          "category": "Electronics"
        }
      }
    ]
  },
  {
    "order_id": "ORD002",
    "order_date": "2025-03-02",
    "customer": "Bob Smith",
    "order_details": [
      {
        "detail_id": "D003",
        "quantity": 3,
        "price": 25.0,
        "product": {
          "product_id": "P003",
          "name": "Bluetooth Headphones",
          "category": "Electronics"
        }
      },
      {
        "detail_id": "D004",
        "quantity": 1,
        "price": 200.0,
        "product": {
          "product_id": "P004",
          "name": "Smart Watch",
          "category": "Wearables"
        }
      }
    ]
  },
…
]

Write an esProc script to access the orders data:

	A
1	=httpfile("http://172.20.10.7:8503/orders").read()
2	=json(A1)

Here, A1 uses the httpfile function to access the RESTful service and read the order data, and A2 uses the json function to convert the JSON string into a two-dimensional table sequence.

Press Ctrl+F9 to run the script:

The data is accessed successfully. This script uses the httpfile function in A1 to access the RESTful service and read the order data, and the json function in A2 to convert the JSON string into a two-dimensional table sequence.

Now, let’s perform a calculation: filter orders that include a specific category (e.g., ‘Electronics’) and have an order amount of at least 200.

Set script arguments:

Write the script:

	A
1	=httpfile("http://192.168.2.52:8503/orders").read()
2	=json(A1)
3	=A2.select(order_details.select@1(product.category==categ) && order_details.sum(price*quantity)>200)
4	=A3.new(order_id,order_date,customer,order_details.sum(price*quantity):amount)

A3 performs conditional filtering. Here, the dot operator is used to directly reference the next layer of data. With dot notation, you can easily navigate through multiple nested layers, making the expression very clear.

Executing the script shows the results of each step:

This can also be written in a single line:

=json(httpfile("http://172.20.10.7:8503/orders").read()).select(order_details.select@1(product.category==categ) && order_details.sum(price*quantity)>200). new(order_id,order_date,customer,order_details.sum(price*quantity):amount)

It’s worth noting that even with DuckDB SQL, which offers relatively good JSON support, this task remains challenging (leaving aside the complexity of accessing the RESTful service):

SELECT
    o.order_id, 
    LIST_FILTER(o.order_details, x -> x.product.category = 'Electronics') AS order_details
FROM read_json_auto(orders.json') AS o
WHERE 
    ARRAY_LENGTH(LIST_FILTER(o.order_details, x -> x.product.category = 'Electronics')) > 0
    AND SUM(
        LIST_FILTER(o.order_details, x -> x.product.category = 'Electronics') -> 
            (x -> x.price * x.quantity)
) > 200;

In contrast, esProc is much simpler and also offers very convenient access to RESTful services.

Security control

To ensure data security, some REST servers authenticate the identity of clients accessing the data. Only authenticated clients can retrieve data. Common authentication methods fall into two main categories: Cookie-based authentication and Token-based authentication. For Cookie-based authentication, after the user accesses the authentication page, the server records the authenticated information in sessions and sends the session ID back to the client’s cookie, or it sends the authentication information back to the client’s cookie. When accessing data on pages with access control, the authentication information saved in the cookie must be included in the request header, allowing the server to determine the identity of the accessor and decide whether to grant access to the page data. For token-based authentication, after the user accesses the authentication page, the server returns an access token. Within the token’s validity period, when accessing data on pages with access control, the token is simply passed as a parameter.

Here are two examples:

The server stores authentication information with session and cookie

	A
1	=httpfile("https://192.168.2.52:8503/login4get?nameOrEmail=tom&userPassword="+md5("mypass")+"&rememberLogin=true")
2	=A1.read()
3	=A1.property("Set-Cookie")
4	=httpfile("https://192.168.2.52:8503/dataid/1628656263716";"Cookie":A3)
5	=A4.read()

The server returns an access token

	A
1	=httpfile("https://192.168.2.52:8503","{\"userId\":\"abc\",\"password\":\"sdfikje87kd908\"}";"Content-Type":"application/json")
2	=A1.read()
3	=json(A2).accessToken
4	=httpfile("https://192.168.2.52:8503","{\"accessToken\":\""+A3+"\",\"other\":\"xxx\"}";"Content-Type":"application/json")
5	=A4.read()

Application integration

Now let’s integrate esProc into a Java application.

Locate the esProc JDBC-related jars, esproc-bin-xxxx.jar and icu4j_60.3.jar, in the [esProc installation directory]\esProc\lib directory.

Place the two jars in the classpath of the Java development environment, and the MySQL driver package in the application.

Next, locate the esProc configuration file, raqsoftConfig.xml, in the [esProc installation directory]\esProc\config directory and place it in the classpath of the Java development environment.

Write Java code to call the SPL script getRestData.splx:

public class RestDataQuery {
    public static void main(String[] args) {
        String driver = "com.esproc.jdbc.InternalDriver";
        String url = "jdbc:esproc:local://";
        try {
            Class.forName(driver);
            Connection conn = DriverManager.getConnection(url);
            PreparedStatement st =conn.prepareCall("call getRestData(?)");
            st.setObject(1, "Electronics");
            st.execute();
            ResultSet rs = st.getResultSet();
            System.out.println("order_id\torder_date\t\tcustomer\t\tamount");
            System.out.println("----------------------------------------------");
            while(rs.next()) {
                String orderId = rs.getString("order_id");
                String orderDate = rs.getString("order_date");
                String customer = rs.getString("customer");
                double amount = rs.getDouble("amount");
                System.out.printf("%-15s%-15s%-15s%.2f%n",orderId, orderDate, customer, amount);
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
}

Using JDBC is completely consistent with database usage. Calling SPL scripts is also similar to accessing stored procedures; the syntax here is call getRestData (?) with parameters passed accordingly.

The execution results are shown below:

In summary, using esProc to process JSON from RESTful interfaces simplifies the parsing and computation of multi-layered structures, and it can be easily embedded in Java applications, serving as an in-application computing engine.