JSON is an easy-to-understand structured data format. It is used to send data in various computer languages in the form of a large string. SPL (Structured Process Language) offers json() function to load JSON as a table sequence object or a sequence object or to generate JSON for computations.

Loading JSON

JSON data types include array, object, numeric value, string, boolean (true and false), and null for representing an empty value. Almost all of them have counterparts in SPL.

Basic data types

SPL has all data types corresponding to JSON basic data types. In the following code, A1 defines a JSON string (A SPL cell defines a string by beginning with a single quote), where data of different types forms a JSON array:

	A
1	'["v1",66,true,false,null]
2	=json(A1)

JSON does not have a special datetime type. It could use a formalized string of any format or milliseconds of numeric type to send datetime data:

	A
1	'["string","2021-01-02","2021/01/03     12:15",1632627573336,66]
2	=json(A1)

SPL can convert a loaded formatted string or numeric value into its own datetime type data using date()and datetime() functions:

	A
3	=[date(A2(2)),     datetime(A2(3),"yyyy/MM/dd HH:mm"), date(A2(4))]

 

Array

In the above section, we put various types of data into one JSON array. Now we define a JSON array of a arithmetic progression:

	A
1	'[11,13,15,17]
2	=json(A1)

Object

In the following code, A1 is a JSON object:

	A
1	'{"n1":"v1","n2":"v2","n3":"v3"}
2	=json(A1)

It is loaded as a single-row table sequence object:

Array of objects

An array of multiple JSON objects will be loaded as a multirow table sequence object:

	A
1	'[{"n1":"v1","n2":"v2","n3":"v3"},     {n1:"v4",n2:"v5",n3:"v6"},   {"n1":"v7","n2":"v8","n3":"v9"}]
2	=json(A1)

In the above code, attribute names in the second row are not enclosed in double quotes. This is not standard JSON. Yet no error is reported and attribute names are correctly parsed:

 

Array of different-structure objects

Sometimes the objects in a JSON array have different structures (as shown below). The first object does not have n3 attribute. There is the n1 attribute in the second object. And the third object does not contain n2 attribute.

	A
1	'[{n1:"v1",n2:"v2"},{n2:"v5",n3:"v6"},{n1:"v7",n3:"v9"}]
2	=json(A1)

In this case, it will be loaded as a table sequence that takes the concatenation of all JSON object attributes as its structure and where the missing values are recorded as nulls (as shown below). The standard format makes it convenient to perform the subsequent computations.

Nested array/object

Elements in a JSON array can be any data types, including the nested array or object.

	A
1	'[[1,2,3],{"n1":"v1","n2":"v2","n3":"v3"},"v1",66,true,null]
2	=json(A1)

Elements of a sequence can be of mixed data types. As the following shows, the first element is also a sequence and the second one is a table sequence.

 

Likewise, attribute values in a JSON object can be nested arrays or objects, too.

	A
1	'{"n1":"v1","n2":[1,2,3],"n3":{"n31":"v31","n32":"v32","n33":"v33"}}
2	=json(A1)

The flexible nested arrays and objects make up multilevel structured data that is easy to express in both JSON and SPL.

Generating JSON strings

The inverse operation of loading JSON is to generate JSON strings. This is achieved also through json()function in SPL. In the following code, A2 loads A1’s JSON string as a SPL object; A3 converts A2’s SPL object back into a JSON string. The json() function judges data type of the passed-in parameter and auto-selects a proper conversion action (It converts into a SPL object when a JSON string is passed in, and a JSON string when a SPL object is passed in).

	A
1	'[[1,2,3],{"n1":"v1","n2":"v2","n3":"v3"},"v1",66,true,null]
2	=json(A1)
3	=json(A2)

After the code is executed, we check A3’s value and find a JSON string same as A1:

[[1,2,3],{"n1":"v1","n2":"v2","n3":"v3"},"v1",66,true,null]

 

Now let’s look at the correspondence between a SPL object and a JSON object when we are trying to generate JSON.

Basic data types

	A
1	=["str",22,true,false,null,date(2021,9,18)]
2	=json(A1)

In the SPL sequence generated in A1, except for the basic JSON data types, there is a date type value. The value will be formatted into a string type when the sequence is converted into a JSON string, as the following shows:

 

Array

In the following code, A1 generates a SPL sequence; A2 uses json() function to generate a JSON string from it.

	A
1	=["a","b","c"]
2	=json(A1)

Object

In the following code, A1 creates an empty table sequence made up of attributes n1, n2 and n3; and A2 inserts two records into A1.

	A
1	=create(n1,n2,n3)
2	>A1.insert(0,"v1","v2","v3"),A1.insert(0,"v4","v5","v6")
3	=json(A1(2))

Each record in A1 corresponds to one JSON object. A3 converts the second record into a JSON string.

 

Array of objects

A SPL sequence or table sequence will be converted into a JSON array when it is exported. Take the table sequence in the above section as an example, A3 all its data as a JSON array of objects.

	A
1	=create(n1,n2,n3)
2	>A1.insert(0,"v1","v2","v3"),A1.insert(0,"v4","v5","v6")
3	=json(A1)

Nested array/object

	A
1	=create(n1,n2,n3)
2	>A1.insert(0,"v1","v2","v3")
3	=create(f1,f2)
4	>A3.insert(0,22,A1)
5	=json(A3)

A3 generates a nested table sequence, as shown below:

A5 generates a nested JSON string:

We can see the structure clearer by formatting it. Even if the two table sequences in both inner layer and outer layer have one row of data, they are converted into the arrays.

[{

         "f1": 22,

         "f2": [{

                  "n1": "v1",

                  "n2": "v2",

                  "n3": "v3"

         }]

}]

Instead of an array of objects, sometimes we expect to generate an object as follows from such a single-row object. To do that, we just need to replace table sequences with records (In the following code, A1 and A3 are table sequences, and A1(1) and A3(1) are their first records).

{

         "f1": 22,

         "f2": {

                  "n1": "v1",

                  "n2": "v2",

                  "n3": "v3"

         }

}

	A
1	=create(n1,n2,n3)
2	>A1.insert(0,"v1","v2","v3")
3	=create(f1,f2)
4	>A3.insert(0,22,A1(1))
5	=json(A3(1))