Background

It is convenient to exchange data via RESTful, but there is a little troublesome about how to compute the received data. SPL offers HTTP interface to directly read RESTful data and compute it.

As the following example shows, in the ecommerce business, orders data access is encapsulated as the REST interface to be accessed by other business systems.

Find the example in the following website:
http://111.198.29.168:8503/getOrders http://111.198.29.168:8503/getOrders/bDate/eDate

Where bDate and eDate are path parameters, which can be formed directly in URL; the parameters specify the beginning date and the ending date for the query.

Below is the JSON-format data returned via the interface:

[
  {
    "order_id": "AMZ123001",
    "order_date": "2023-11-11 18:00:17",
    "order_status": "processing",
    "user_info": {
      "user_id": "USR001",
      "username": "john_doe",
      "email": "john_doe@example.com",
      "country": "USA",
      "city": "New York"
    },
    "payment_info": {
      "payment_method": "credit_card",
      "total_amount": 0
    },
    "order_details": [
      {
        "product_id": "PD001",
        "product_name": "Smartphone",
        "price": 599.99,
        "quantity": 1
      },
      {
        "product_id": "PD002",
        "product_name": "Laptop",
        "price": 1299.99,
        "quantity": 1
      }
    ]
  },

…
]

Now we parse orders data in the year 2023. We want to find users whose order amounts rank in top 5 in the current year from all uncanceled orders and give them rewards.

Implementation process

Edit the script

Open SPL IDE to edit the script:

A1-A2: RESTful accesses authentication.

A3: Access data interface to obtain the current year’s orders data according to the specified beginning date and ending date. bDate and eDate are script parameters.

A4: Structuralize the JSON strings to convert them to a multilevel table sequence.

The level next to User_info:

A5: Perform filtering and return all uncanceled orders.

A6: Group A5’s records by user and calculate sum of order amounts for each user. Here user_info.user_id method is used to access different levels of data in the multilevel table sequence (the multilevel table sequence naturally fits the multilevel JSON data).

A7: Get users whose total order amounts rank in top 5 (the result includes user id and amount).

A8: Return result as JSON format.

Invocation from Java

Refer to How to Call an SPL Script in Java and import esproc-bin-xxxx.jar and icu4j-60.3.jar and the configuration file raqsoftConfig.xml to the project, and then invoke the SPL script or execute the SPL statement via JDBC.

Execute the SPL script file as we call the stored procedure:

Class.forName("com.esproc.jdbc.InternalDriver");
Connection conn= DriverManager.getConnection("jdbc:esproc:local://");
PreparedStatement st = conn.prepareCall("call restJsonParse (?,?)");
st.setObject(1, "2023-01-01");
st.setObject(2, "2023-12-31");
st.execute();
ResultSet rs = st.getResultSet();

Or execute the SPL statement directly. Select the script in IDE and copy and paste it to the Java string and we can get the following string:

String spl = "==httpfile(\"http://111.198.29.168:8503/login4get?nameOrEmail=tom&userPassword=\"+md5(\"mypass\")+\"rememberLogin=true“:\"UTF-8\" ).read()\n" +
        "=A1.property(\"Set-Cookie\")\n" +
        "=httpfile(\"http://111.198.29.168:8503/getOrders\"+\"/\"+bDate+\"/\"+eDate:\"UTF-8\";\"Cookie\":A2).read() \n" +
        "=json(A3)\n" +
        "=A4.select(order_status!=\"canceled\")\n" +
        "=A5.groups(user_info.user_id;sum(order_details.price*order_details.quantity):amt)\n" +
        "=A6.top(-5;amt)\n" +
        "return json(A7)";

Note that the script strings should be preceded by double equal sign (==).

Now we execute the script:

Class.forName("com.esproc.jdbc.InternalDriver");
Connection conn= DriverManager.getConnection("jdbc:esproc:local://");
Statement st = conn.createStatement();
ResultSet rs = st.executeQuery(spl);

Output the result set and we get the following JSON string:

[{"user_id":"USR001","amt":2399.96},{"user_id":"USR008","amt":1079.8799999999999},{"user_id":"USR005","amt":999.96},{"user_id":"USR010","amt":879.92},{"user_id":"USR007","amt":799.96}]

Now we finish parsing and computing the RESTful data. The computing result can be directly returned or be passed to another application for use.

Appendix:

orders.json
restJsonParse.splx
RestfulJsonParse.java