Convert nested arbitrary JSON to CSV in Java
This question has been asked many times but I couldn't find the answer that fixes my issue.
I'm trying to convert nested JSON format to CSV format like this :
The JSON structure is arbitrary and could be anything , nested or not.
I'm not suppose to know it, it's a database answer and I need to export this JSON answer into CSV file.
Here is an example
Input :
{
"_id": 1,
"name": "Aurelia Menendez",
"scores": [
{
"type": "exam",
"score": 60.06045071030959
},
{
"type": "quiz",
"score": 52.79790691903873
},
{
"type": "homework",
"score": 71.76133439165544
}
]
}
The output I'm looking for :
_id,name,scores.type,scores.score,scores.type,scores.score,scores.type,scores.score
1,Aurelia Menendez,exam,60.06...,quiz,52.79...,homework,71.76..
This is an example, it could be any other JSON document.
The idea here is to use dot notation in the CSV column name.
I've already used CDL but the output is not what I want :
_id scores name
1 "[{score:60.06045071030959,type:exam},{score:52.79790691903873,type:quiz},{score:71.76133439165544,type:homework}]" Aurelia Menendez
So how can I convert nested JSON to CSV with dot notation and in a generic way ?
Edits
Deserialisation of the JSON with Jackson :
ObjectMapper mapper=new ObjectMapper();
JsonNode jsonNode=mapper.readValue(new File("C:.........test.json"), JsonNode.class);
Ismail
Converting JSON to XLS/CSV in Java has what you are looking for.
Basically, you need to use org.json.CDL to convert from JSON to CSV format
Comments are not convenient place to post longs answers, so I post my answer here.
Analyze your JSON and all possible JSON structures you can get from your database. It should be a limited number of JSON forms.
As you have analyzed your JSON structure build a class/class hierarchy, that fully reflects this structure.
Use JSON serializer/deserializer library at your choice, to deserialize JSON to a java object.
Employ StringBuffer/StringBuilder classes, and iterate over your object information, and build comma delimited (or tab-delimited) strings.
Write strings you have built on the previous stage to the file.
That's it.
Like you said :
The JSON structure is arbitrary and could be anything , nested or not.
The JSON to CSV conversion can't be generalized as it varies from user to user and also depends specific requirements.
But still there's a library json2flat which tries to achieve it. But it may differ from user's requirement. Still it's worth a try.
For example for the JSON given above:
{
"_id": 1,
"name": "Aurelia Menendez",
"scores": [
{
"type": "exam",
"score": 60.06045071030959
},
{
"type": "quiz",
"score": 52.79790691903873
},
{
"type": "homework",
"score": 71.76133439165544
}
]
}
can be interpreted as follows :
/_id,/name,/scores/type,/scores/score
1,"Aurelia Menendez","exam",60.06045071030959
1,"Aurelia Menendez","quiz",52.79790691903873
1,"Aurelia Menendez","homework",71.76133439165544
上一篇: 包含生成的生成文件，不带警告消息