Convert nested arbitrary JSON to CSV in Java

Question

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

Solution 1

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

Solution 2

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