Convert a JSON File into a Two-field (Level Names and Corresponding Values) CSV File

Problem description & analysis

Below is JSON file json.json:

{

       "strings": {

              "1level1": {

                     "1level2": {

                            "1label1": "1value1",

                            "1label2": "1value2"

                     }

              },

              "2level1": {

                     "2level2": {

                            "2level3": {

                                   "2label1": "2value1"

                            },

                            "2label2": "2value2"

                     }

              }

       }

}

We are trying to convert the JSON file into a CSV file consisting of two columns, level names and corresponding values. The original level numbers will be parsed as dot-separated key column values and values as value column values, as shown below:

Keys,Default

1level1.1level2.1label1,1value1

1level1.1level2.1label2,1value2

2level1.2level2.2level3.2label1,2value1

2level1.2level2.2label2,2value2

Solution

Write the following script p1.dfx in esProc:

A

B

C

1

=file("json.json").read().import@j().strings

2

=create(Keys,Default)

3

>func(A4,A1,null)

4

func

5

if   ifr(A4)

>A4.fno().(func(A4,A4.field(~),if(B4,B4/".")/A4.fname(~)))

6

else

>A2.insert(0,B4,A4)

7

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

Explanation:

A1  Import the JSON file as a multilevel table sequence and get strings values.

A2  Create an empty table sequence made up of Keys column and Default column. The former contains level names and the latter contains corresponding values.

A3  Call A4’s subprogram, during which A1 and null are two parameters.

A4  Define a subprogram.

B5  If the first parameter in A5’s subprogram is a record, execute cell C5.

C5  Loop through each column of the first parameter in the subprogram, call subprogram A4 for which value of the current filed and the cumulated, dot-separated field names are parameters.

B6  If the first parameter in A5’s subprogram isn’t a record, execute cell C6.

C6  Insert the accumulated, dot-separated field names and the current values to A2’s table sequence.

A7  Export A2’s result to json.csv.

Read How to Call an SPL Script in Java to learn about the method of integrating a SPL script with a Java program.

Q & A Collection

https://stackoverflow.com/questions/63452677/convert-json-to-different-looking-csv-in-java