How to Convert JSON Data into a Two-dimensional Table and Export It to a CSV

Problem description & analysis

Below is data in JSON file json.json:

To convert the multilevel JSON file into a two-dimensional structure while splitting the two subfields under identities field and promoting them to the parent level, and store data as a CSV file as follows:

undefined

Solution:

We write the following script p1.dfx in esProc:

A

1

=file("json.json").read()

2

=json(A1).users.new(displayName,givenName,surname,extension_user_type,identities.signInType:issuerType,identities.issuerAssignedId,extension_timezone,extension_locale,extension_tenant)

3

=file("json.csv").export@tc(A2)

Code description:

A1   Read data of the JSON file as a string.

A2  Parse the string into a multilevel table sequence according to the original structure while splitting and promoting identities.signInType and identities.issuerAssignedId to their parent level with the new function.

A3  Export A2’s result to json.csv.

See How to Call an SPL Script in Java to learn about the way of integrating the SPL code with a Java program.

Q & A Collection

https://stackoverflow.com/questions/63920843/multidimentional-json-to-csv-non-nested-csv-convertion-working-in-java