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

CDL but I'm not free to set parameters so I can have the output I want. — Ismail Sen, Jul 16 '14 at 10:16
Have you considered actually writing some code? (Yes, I know that's a rash thing to suggest.) — Hot Licks, Jul 16 '14 at 12:39
@HotLicks Yes. I've tried with CDL,OpenCSV,Jackson and GSON. — Ismail Sen, Jul 16 '14 at 12:42
@IsmailSen please update your question and state that JSON structure is not predefined, and you can get JSON with arbitrary structure and you need to process it. — , Jul 16 '14 at 12:48
@IsmailSen I think that forms of that JSON are limited. Analyze forms of JSON structure you can get from the database, and declare java class or java classes which can reflect this structure. And after that use GSON or another deserializer to process this json structure. — , Jul 16 '14 at 13:03
@IsmailSen Rafael is right there are rules for the structure of json formated text. My $0.02 is that going from a structured document like json or xml to a flat csv file loses information in general. I don't think there is a consistent way to represent arbitrary json in a csv. You will have difficulty translating between the two. — t3dodson, Jul 17 '14 at 06:54
@RafaelOsipov I've deserialize the JSON and I've added the output in the Edits — Ismail Sen, Jul 17 '14 at 07:03

score 3 · Answer 1 · edited Dec 10 '16 at 22:18

3

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

edited Dec 10 '16 at 22:18

zx485

28,498
28
50
59

answered Dec 10 '16 at 21:54

skap

493
4
9

1

Suppose that the JSON included another array after `"scores" : [ … ]`, such as perhaps `"classes" : [ … ]` or `"societies" : [ … ]`; how would you convert that? Don't get me wrong: what you suggest is sensible (and different from other answers) for the sample data, but the question does state "JSON structure is arbitrary … nested", so ideally your idea should be readily generalizable. – Jonathan Leffler Dec 10 '16 at 22:22
@JonathanLeffler it all depends upon the user. How they want to have an interpretation of the json. Well for the JSON I here goes another sample :: – skap Dec 10 '16 at 23:12
@JonathanLeffler Here goes the sample as you have asked: `{ "_id": 1, "name": "Aurelia Menendez", "scores": [ { "type": "exam", "score": 60.06045071030959 } ], "classes" : [ { "sub" : "English" } ], "societies" : [ { "socDemo" : "xyz" } ]}`. The output to this JSON will be `/_id,/name,/scores/type,/scores/score,/classes/sub,/societies/socDemo 1,"Aurelia Menendez","exam",60.06045071030959,, 1,"Aurelia Menendez",,,"English", 1,"Aurelia Menendez",,,,"xyz"`. – skap Dec 10 '16 at 23:19

score 2 · Answer 2 · edited May 23 '17 at 12:18

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

edited May 23 '17 at 12:18

Community

1
1

answered Sep 16 '14 at 07:58

ahaaman

552
5
22

score 0 · Answer 3 · answered Jul 17 '14 at 07:26

0

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.

answered Jul 17 '14 at 07:26

Could you please have a look here : http://stackoverflow.com/questions/24799457/csv-generator-does-not-support-array-values-for-properties – Ismail Sen Jul 17 '14 at 10:35
@IsmailSen sorry, but I have no experience with `Jackson` framework.\ – Jul 17 '14 at 10:38
In step 4, how can I iterate over the elements of scores collection ? – Ismail Sen Jul 17 '14 at 11:08
`scores` is a collection of objects? implement a loop, that iterates over this collection and builds a string using StringBuilder or StringBuffer class instance. – Jul 17 '14 at 11:12
In the Edits, I deserialize the JSON into a java object `jsonNode`. When I try to iterate over it, I got `Can only iterate over an array or an instance of java.lang.Iterable`. – Ismail Sen Jul 17 '14 at 11:30
Under word `iterate` I mean making a loop, cycle, to process a set of objects. If you check [JsonNode javadoc](http://jackson.codehaus.org/1.7.9/javadoc/org/codehaus/jackson/JsonNode.html) you will find all necessary methods to get those values. – Jul 17 '14 at 11:41

Convert nested arbitrary JSON to CSV in Java

3 Answers3