【Question】

I have a JSON file whose content looks like this:

I need to sort out the individual fields and write them to the database.

There are a number of child and grandchild records under the LIST” field. Our target fields are “GROUPNAME” and “SERVICES”.

“GROUPNAME” values and “SERVICES”’s “SERVICEID” respectively match Groupname values and ID in database table _groups.

Each value of “SERVICES” corresponds to a record in database table Services.

【Answer】

In your case, the JSON strings have multilevel fields and certain fields contain dynamic data, like LIST and SERVICES, under which the node count varies. What’s more, there are attribute names containing a space, like MOVISTAR SPAIN, or a dot, like Requires.Network. These make it difficult to parse in Java or C#. The Structured Process Language (SPL) encapsulates the functionality of parsing JSON and writing result to the database to make the coding rather easy:

A1: Read in the JSON file as a string and import it as a table sequence.

A2: Get records under SUCCESS.LIST.

A3,A4: Generate two new table sequences for database update to commit.

Create fields according to A2’s field count and get field values (child records) from B5, like ：![].

Run a loop according to the field count in B5.SERVICES to get field names from C7 and field values from C8. Below is B5.SERVICES:

Now we have gotten the “GROUPNAME” values, the “SERVICEID”s under “SERVICIES” and “SERVICIES” values to populate them into A3 and A4’s table sequences:

Results of database write:

services table:

_group table: