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
– Ismail Sen, Commented Jul 16, 2014 at 10:16
Have you considered actually writing some code? (Yes, I know that's a rash thing to suggest.) — Hot Licks
– Hot Licks, Commented Jul 16, 2014 at 12:39
@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. — user784540
– user784540, Commented Jul 16, 2014 at 13:03

zx485 · Accepted Answer · 2016-12-10 22:18:56Z

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, 2016 at 22:18

zx485

29.1k28 gold badges55 silver badges65 bronze badges

answered Dec 10, 2016 at 21:54

skap

5135 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jonathan Leffler Over a year ago

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.

skap Over a year ago

@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 Over a year ago

@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"

.

Community · Accepted Answer · 2017-05-23 12:18:22Z

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, 2017 at 12:18

CommunityBot

11 silver badge

answered Sep 16, 2014 at 7:58

ahaaman

6596 silver badges22 bronze badges

Comments

user784540 · Accepted Answer · 2014-07-17 07:26:50Z

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, 2014 at 7:26

user784540

6 Comments

Ismail Sen Over a year ago

Could you please have a look here : stackoverflow.com/questions/24799457/…

user784540 Over a year ago

@IsmailSen sorry, but I have no experience with Jackson framework.\

Ismail Sen Over a year ago

In step 4, how can I iterate over the elements of scores collection ?

user784540 Over a year ago

scores is a collection of objects? implement a loop, that iterates over this collection and builds a string using StringBuilder or StringBuffer class instance.

Ismail Sen Over a year ago

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.

|

Collectives™ on Stack Overflow

Convert nested arbitrary JSON to CSV in Java

3 Answers 3

3 Comments

Comments

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related