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.
https://stackoverflow.com/questions/63452677/convert-json-to-different-looking-csv-in-java
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_SPL
SPL Learning Material 👉 https://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProc_SPL