Restful is a commonly used data source, and almost all Restful data is of JSON format. So we put them in the same section.

REST service and data formats it uses

Access http://192.168.2.52:8503/orders to get the order data:

The file uses multilevel JSON format to store order-related information. Its structure is as follows:

[
  {
    "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"
        }
      }
    ]
  },
…
]

Computing use case

Find orders that contain the specified product category (such as Electronics) and whose amounts are not blow 200.

Restful

Set script parameters:

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 a conditional filtering, where the dot operator (.) is used to reference sub data at the nearest level. To reference sub data at a farther level, just use the dot one after another until the target level is reached. The representation is clear.

Execute the script and then you can view result of each step:

The script can also be written as one statement:

=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)

JSON

If the data is stored as a JSON file, we only need to change the first line of code as:

=file(“orders.json”).read()

And the subsequent code is the same.

We use the JSON data to classify customers according to their purchase amounts.

	A
1	=json(file("orders.json").read())
2	=A1.groups(customer;order_details.sum(quantity*price):amt)
3	=A2.derive(if(amt>300:"Platinum",amt>200:"Gold",amt>100:"Silver";"Normal"):tier)

A3 Read data from the JSON file and compute total order amount of each customer. A3 divides customers into different levels and returns the following result:

Security control

To ensure data security, some REST servers require identity authentication for all accesses. Only the authenticated accesses can retrieve the data. There are two commonly used types of identity authentication. In the first type, users access the authentication page, and the server stores the authentication information in Session and send the session ID (and sometimes the authentication information) back to the client side as cookies. When users access data in a privileged page, the ID or information stored in cookies is included in the request header to authenticate their identities and judge if they should be allowed to proceed. In the other type, the server returns an access token after the user finishes authentication and during the validity period the token is sent back as a parameter when the user accesses a privileged page.

Here are two examples.

The server stores identity information as cookies or in Session:

	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 a 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()