Constructing JSON Strings
JSON (JavaScript Object Notation) is a lightweight data-interchange format and it is commonly used in REST web service. AlchemyJ provides multiple ways to construct a JSON string. They are as follow:
The above functions are often used to construct a JSON string for the Body parameter of ajWebServiceREST.
ajJSON
ajJSON returns a JSON string given a corresponding name and value pair. If the value of the JSON name-value pair contains multiple records, a JSON array will be returned.
ajJSON function supports both row array and column array.
Case 1: Get a JSON Array with ajJSON
For example, below is a customer list that shall be send as an input for a REST API we want to call. We will use ajJSON to construct the function point response.
ajJSON has four parameters. In this example, we only need to define the first two parameters of ajJSON. Cell B3 to F3 define the header range (first parameter) while cell B4 to F8 defines the data range (second parameter).
=ajJSON(header,data,data_type,exclude_column)
As ajJSON returns a string, highlight the cell you want the data to be returned, type in =ajJSON(B3:F3,B4:F8) and the customer table will be converted to a JSON array as shown below.
[{"Name":"CHAN TAI MAN","Date of Birth":28127,"Gender":"M","Customer Level":"5","Telephone":"22205555"},{"Name":"JERRY SZE","Date of Birth":32239,"Gender":"M","Customer Level":"2","Telephone":"22203333"},{"Name":"MERRY SO","Date of Birth":32205,"Gender":"F","Customer Level":"4","Telephone":"22201111"},{"Name":"MICKY MAI","Date of Birth":32271,"Gender":"F","Customer Level":"3","Telephone":"22206666"},{"Name":"TOM HUI","Date of Birth":28827,"Gender":"M","Customer Level":"1","Telephone":"22204444"}]
Case 2: Get a JSON Object with ajJSON
For the same customer list as Case 1, if you want to get a JSON object, then you can do as follow:
Input a data range in cell C12.
=ajAddress(B3:F3) & "," & ajAddress(B4:F8)
In this example, input the data item name as [customers] in B12. Then use =ajJSON(B12:B12,C12:C12)and you will have a JSON Object as shown below.
{"customers":[{"Name":"CHAN TAI MAN","Date of Birth":28127,"Gender":"M","Customer Level":"5","Telephone":"22205555"},{"Name":"JERRY SZE","Date of Birth":32239,"Gender":"M","Customer Level":"2","Telephone":"22203333"},{"Name":"MERRY SO","Date of Birth":32205,"Gender":"F","Customer Level":"4","Telephone":"22201111"},{"Name":"MICKY MAI","Date of Birth":32271,"Gender":"F","Customer Level":"3","Telephone":"22206666"},{"Name":"TOM HUI","Date of Birth":28827,"Gender":"M","Customer Level":"1","Telephone":"22204444"}]}
Case 3: ajJSON for a column array
ajJSON function can also handle column array. In this example, the customer list is transposed as shown below. B2:B6 is the header range and C2:G6 is the data range.
As ajJSON returns a string, highlight the cell you want the data to be returned to, type in =ajJSON(B2:B6,C2:G6) and the customer table will be converted to a JSON array. It returns the same result as in case 1.
ajJSONFromTable
ajJSONFromTable is a quick way to turn a table of data into a JSON string. For returning multiple tables, use ajJSONFromSchema instead.
=ajJSONFromTable(header,data,data_name,data_type,exclude_column)
Take the same example in Case 2: Get a JSON Object with ajJSON, we can use =ajJSONFromTable(B3:F3,B4:F8,"customers") to achieve the same result.
Return Result:
{"customers":[{"Name":"CHAN TAI MAN","Date of Birth":28127,"Gender":"M","Customer Level":"5","Telephone":"22205555"},{"Name":"JERRY SZE","Date of Birth":32239,"Gender":"M","Customer Level":"2","Telephone":"22203333"},{"Name":"MERRY SO","Date of Birth":32205,"Gender":"F","Customer Level":"4","Telephone":"22201111"},{"Name":"MICKY MAI","Date of Birth":32271,"Gender":"F","Customer Level":"3","Telephone":"22206666"},{"Name":"TOM HUI","Date of Birth":28827,"Gender":"M","Customer Level":"1","Telephone":"22204444"}]}
ajJSONFromSchema
ajJSONFromSchema is good for constructing JSON string from tables that are related based on the definition of a Data Relationship Schema which needs to be defined first.
=ajJSONFromSchema(data_relationship_header,data_relationship)
Here is a sample to construct a JSON string for Client and Order tables.
There are 3 records in Client tables and the key is Client ID. There are also 3 records in Order table and the foreign key is Client ID.
Data Relationship Schema must follow a specific format. Refer to Data Relationship Schema for the columns required. In this example, A15:I15 is the Data Relationship Schema header and A16:I17 is the Data Relationship Schema data range. Enter the formula =ajJSONFromSchema(A15:I15,A16:I17) in cell B20.
The function generates a JSON string with a hierarchy based on the Data Relationship Schema. In this example, the order table is linked to the client table based on client ID. The JSON result shows that only client 1234567890001 has an order list since the order table only contains client 1234567890001.
Notice that birthday nodes become numbers instead of date strings. This is because we have not defined the data type of each field. To define the data type, specify the data dictionary address in the data relationship schema table and define the data type of each field in data dictionary.