Converting JSON to CSV

Question

I have a JSON of the format below and I am trying to write to CSV:

{
    "results": [{

            "geo_position": {
                "Field1": 11,
                "Filed2": 12
            },
            "Field3": 13,
            "Filed4": 14,
            "Field5": 15
        },

        {
            "geo_position": {
                "Field1": 21,
                "Filed2": 22
            },
            "Field3": 23,
            "Filed4": 24,
            "Filed5": 25
        }
    ]
}

---

I am expecting output like:

field1,field2,field3,field4,field5
11,12,13,14,15
21,22,23,24,25

---

I am getting output CSV as below:

    geo_position,field3,field4,field5
   {Field1:11,Field2:12}, 13,14,15
   {Field2:21,Field2:22},23,24,25

---

My java code:

JSONObject jsonObj = new JSONObject(jsonArray);
System.out.println(jsonObj);
JSONArray docs = jsonObj.getJSONArray("results");
File file=new File("C:/fromJSON2.csv");
String csv = CDL.toString(docs);
FileUtils.writeStringToFile(file, csv);

Can some one help me to figure out why I am getting in different format. What should I do to get in the format I expect?

Answer 1

You can use my solution:

public static void main(String[] args) {
    JSONObject jsonObj = new JSONObject(jsonArray);
    JSONArray docs = jsonObj.getJSONArray("results");
    File file = new File("C:/fromJSON2.csv");
    String csv = getDocs(docs);
    FileUtils.writeStringToFile(file, csv);
  }

  public static String getDocs(JSONArray ja) throws JSONException {
    String result = "";
    Map<String, Integer> map = new HashMap<>();
    for (int i = 0; i < ja.length(); i++) {
      JSONObject jo = ja.optJSONObject(i);
      if (jo != null) {
        getAllTopKeyAndValue(jo, map);
        if (i == 0) {
          result += keyOfMap2String(map) + "\n";
        }
        result += valueOfMap2String(map) + "\n";
      }
    }
    return result;
  }


  public static void getAllTopKeyAndValue(JSONObject jo, Map<String, Integer> map) {
    if (jo != null) {
      JSONArray names = jo.names();
      String string = "";
      List integers = new ArrayList<>();
      if (names != null) {
        for (int i = 0; i < names.length(); i++) {
          String name = names.getString(i);
          JSONObject object = jo.optJSONObject(name);
          if (object != null) {
            getAllTopKeyAndValue(object, map);
          } else {
            map.put(name, (Integer) jo.get(name));
          }
        }
      }
    }
  }

  public static String keyOfMap2String(Map<String, Integer> map) {
    String result = "";
    Iterator<Map.Entry<String, Integer>> iter = map.entrySet().iterator();
    while (iter.hasNext()) {
      Map.Entry<String, Integer> entry = iter.next();
      result += entry.getKey();
      if (iter.hasNext()) {
        result += ",";
      }
    }
    return result;
  }

  public static String valueOfMap2String(Map<String, Integer> map) {
    String result = "";
    Iterator<Map.Entry<String, Integer>> iter = map.entrySet().iterator();
    while (iter.hasNext()) {
      Map.Entry<String, Integer> entry = iter.next();
      result += entry.getValue();
      if (iter.hasNext()) {
        result += ",";
      }
    }
    return result;
  }

Answer 2

There are some typos in the data.

You can try json2flat for converting JSON docs to get an equivalent CSV representation.
If you want to try for more JSON doc click here.

For the JSON data :

{
    "results": [{

            "geo_position": {
                "Field1": 11,
                "Field2": 12
            },
            "Field3": 13,
            "Field4": 14,
            "Field5": 15
        },

        {
            "geo_position": {
                "Field1": 21,
                "Field2": 22
            },
            "Field3": 23,
            "Field4": 24,
            "Field5": 25
        }
    ]
}

Equivalent CSV representation :

results/Field3,results/Field4,results/Field5,results/geo_position/Field1,results/geo_position/Field2
13.0,14.0,15.0,11.0,12.0
23.0,24.0,25.0,21.0,22.0

The code is also preety simple.

JFlat flatMe = new JFlat(jsonString);
flatMe
    .json2Sheet()
    .headerSeparator("/")
    .write2csv("test.csv");

This will write the result to test.csv file.

Answer 3

your JSON structure is

{
"results":
[
    {
        "geo_position": {"Field1":11,"Filed2":12},
        "Field3":13,
        "Filed4":14,
        "Field5":15
    },

    {
        "geo_position":{"Field1":21,"Filed2":22},
        "Field3":23,
        "Filed4":24,
        "Filed5":25
    }

]
}

For it to work about the way you want it to work, it should be something like:

{
"results":
[
    {
        "Field1":11,
        "Filed2":12,
        "Field3":13,
        "Filed4":14,
        "Field5":15
    },

    {
        "Field1":21,
        "Filed2":22,
        "Field3":23,
        "Filed4":24,
        "Filed5":25
    }

]
}

what you could do something like

for(int i = 0; i<resultsJSONArray.length(); ++i){ 
    if(resultsJSONArray.get(i).has("geo_position")) {
          String names[] = JSONObject.getNames(resultsJSONArray.get(i).get("geo_position")));
          for(int i = 0; i<names().length; ++i) {                                       
              resultsJSONArray.get(i).put(names[i],resultsJSONArray.get(i).get("geo_position").get(names[i]));
          }
          JSONObject.getNames(resultsJSONArray.get(i).remove("geo_position"));
    }
}