Convert nested arbitrary JSON to CSV in Java

Question

This question has been asked many times, but I could not find an answer that fixes my problem.

I am trying to convert a nested JSON format to CSV format as follows:

The JSON structure is arbitrary and can be anything , nested or not.

I should not know this, this is a response to the database, and I need to export this JSON response to a 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 result 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 can be any other JSON document.

The idea here is to use dot notation in the CSV column name.

I already used CDL, but the result is not the one 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 dotted notation and a common way?

edits

Deserialization JSON with Jackson :

  ObjectMapper mapper=new ObjectMapper(); JsonNode jsonNode=mapper.readValue(new File("C:\\...\\...\...\\test.json"), JsonNode.class); 

Ismail

Answer 1

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

Answer 2

Comments are not suitable for posting answers to debt questions, so I post my answer here.

• Analyze your JSON and all the possible JSON structures that you can get from your database. This should be a limited number of JSON forms.

• As you have analyzed the JSON structure, create a class / class hierarchy that fully reflects this structure.

• Use the JSON serializer / deserializer library of your choice to deserialize the JSON for the Java object.

• Use the StringBuffer / StringBuilder classes, iterate over your object information, and also strings separated by commas (or tab delimiters).

• Write the lines that you created in the previous step to a file.

What is it.

Answer 3

As you said:

The JSON structure is arbitrary and can be anything , nested or not.

Converting JSON to CSV cannot be generalized, as it varies from user to user, and also depends on specific requirements.

But still there is a json2flat library that is trying to achieve it. But this may differ from the user's requirements. Still worth a try.

For example, for the JSON 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