Multidimentional JSON to CSV , non-nested CSV convertion working in JAVA

Question

I have a JSON file which I need to flat and create a CSV from it. I was able to convert to CSV (I have a "users" wrapper) but inside "users" there is another wrapper named "identifiers", I want to iterate them as well and create a field for them.

I have a JSON file which looks like this :

{
  "users": [
    {
      "displayName": "Sharad Dutta",
      "givenName": "",
      "surname": "",
      "extension_user_type": "user",
      "identities": [
        {
          "signInType": "emailAddress",
          "issuerAssignedId": "kkr007@gmail.com"
        }
      ],
      "extension_timezone": "VET",
      "extension_locale": "en-GB",
      "extension_tenant": "EG12345"
    },
    {
      "displayName": "Wayne Rooney",
      "givenName": "Wayne",
      "surname": "Rooney",
      "extension_user_type": "user",
      "identities": [
        {
          "signInType": "userName",
          "issuerAssignedId": "kkr007"
        }
      ],
      "extension_timezone": "VET",
      "extension_locale": "en-GB",
      "extension_tenant": "EG12345"
    }
  ]
}

I am trying to convert the JSON to CSV and this is what I was able to do :

Below is the code : As you can see, my JSON is wrapped inside a "users" type wrapper and in the JSON i have one more wrapper "identities", with the code that I did, I am able to iterate but the "identites" is coming out as JSON blob, I want something like this in place of identites

issuerType       issuerAssignedId
bla bla bla      bla bla bla

and not a JSON nested blol for identites.

public static void main(String[] args) throws JSONException {

        String userJsonFile = "C:\\Users\\Administrator\\Desktop\\jsonRes\\json_format_user_data_input_file.json";

        try {
            userJsonAsString = readFileAsAString(userJsonFile);
        } catch (Exception e1) {
            e1.printStackTrace();
        }
        JSONObject output;
        try {
            output = new JSONObject(userJsonAsString);
            JSONArray docs = output.getJSONArray("users");
           
            File file = new File("C:\\Users\\Administrator\\Desktop\\jsonRes\\EmpDetails.csv");
            String csv = CDL.toString(docs);
            
            FileUtils.writeStringToFile(file, csv);
            System.out.println("Data has been Sucessfully Writeen to " + file);
            System.out.println(csv);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    private static String readFileAsAString(String inputJsonFile) throws Exception {
        
        return new String(Files.readAllBytes(Paths.get(inputJsonFile)));
    }

Answer 1

Welcome to SO! As you rightly said, "identities" is a nested JSON Array inside each element of the "users" array. So, when you flatten into a more-or-less relational format (here CSV), you would typically need to repeat the rest of the info for each element of the "identitites" array.

Now, whichever JSON parsing library you are using (JSONObject in you snippet, I am assuming comes from org.json jar?), you would need to iterate through the JSONArray docs and for each element call the getJSONArray("identities").

Since it is a nested array, you would need two loops to handle this scenario.

outer loop for the "users" array and a nested loop for the "identities" on each element of the "users" array.

Please use the below snippet as a reference only. Have written according to your code snippet. Please use standard variable naming and practices. This is just to show you the logic

String userJsonAsString="";
    StringBuilder sBuild = new StringBuilder();
    StringBuilder sBuild2 = new StringBuilder();
    try {
        userJsonAsString  = readFileAsAString(userJsonFile);
    } catch (Exception e1) {
        e1.printStackTrace();
    }
    JSONObject output;
    try {
        output = new JSONObject(userJsonAsString);
        JSONArray docs = output.getJSONArray("users");
        
        Iterator<Object> iter =   docs.iterator();
        
        while(iter.hasNext()) {
            JSONObject userEleObj = (JSONObject)iter.next();
             JSONArray nestedIdArray = userEleObj.getJSONArray("identities");
             Iterator<Object> nestIter = nestedIdArray.iterator();
             
             while(nestIter.hasNext()) {
                 JSONObject identityEleObj = (JSONObject)nestIter.next(); 
                 identityEleObj.keySet().stream().forEach(key -> sBuild2.append(identityEleObj.get(key)+","));
                 userEleObj.keySet().stream().forEach(key -> {
                     if(StringUtils.equals(key, "identities")) {
                         sBuild.append(sBuild2.toString());
                         sBuild2.replace(0, sBuild2.length(), "");
                     } else {
                         sBuild.append(userEleObj.get(key)+","); 
                     }
                     
                     
                 });
                 
             }
             sBuild.replace(sBuild.lastIndexOf(","), sBuild.length(), "\n");
             
        }
       
   
        System.out.println(sBuild);
    } catch (Exception e) {
        e.printStackTrace();
    }