Flatten Complex JSON

Discussion in 'Mac Programming' started by MagnumOP, Sep 26, 2013.

  1. MagnumOP macrumors regular

    Joined:
    Jul 5, 2010
    #1
    I am working with some complex JSON(arrays nested in arrays). I am interested in flattening the JSON structure into a CSV. My main issue is that the full structure of the JSON is unknown, arrays vary in length and complexity.

    Is there either:
    1. A tool that will scan the JSON and create a complete dictionary?
    OR
    2. A tool that can flatten the JSON into CSV or some other 'flat' data structure?
     
  2. MagnumOP thread starter macrumors regular

    Joined:
    Jul 5, 2010
    #2
    So... there seems to be at least some interest in this thread based on how many people have read it.

    I was able to solve my problem, although what I have done is not elegant.

    I essentially identified the high level arrays/structure manually. Then I wrote/found code online that would implode arrays and multidimensional arrays(coding done in php).

    If anyone is interested in the code I would be glad to post it.
     
  3. MeUnix macrumors 6502

    Joined:
    Aug 21, 2013
    Location:
    San Francisco
    #3
    I'd be interested in seeing it just so I know how you were able to "flatten" the JSON structure into a CSV, as you put it.
     
  4. MagnumOP, Oct 6, 2013
    Last edited: Oct 6, 2013

    MagnumOP thread starter macrumors regular

    Joined:
    Jul 5, 2010
    #4
    Code:
    //function to implode nested arrays
    
    function multiImplode($array) {
        $ret = '';
    	$glue = ', ';
    	
       	if(is_array($array)){
    	    foreach ($array as $item) {
       		    if (is_array($item)) {
       	    	    $ret .=  multiImplode($item, $glue) . $glue;
       		    } else {
       	      	    $ret .= $item . $glue;
       	      	    }
       		    }
    	 	}
        $ret = substr($ret, 0, 0-strlen($glue));
        return $ret;
    }
    
    //function to check if a value is an array or missing
    //implodes arrays or returns null
    
    function helperNull ($val){
    	
    	if(is_array($val)){$val = multiImplode($val);}
    	if(!$val || $val == ""){
    		$val = "null";
    	}
     	$connectionString = "host=localhost port=5432 dbname=JSON user=username password=somepassword";
     	$dbConnection = pg_connect($connectionString);
     	$val = pg_escape_string($dbConnection, $val);
     	return $val;
    }
    
    
    //read in the file one line at a time. In my case each line was an individual JSON record.
    
    $file_handle = fopen("my_file.json", "r");
    
    //insert processed JSON into a postgres database. From here it is easy to export from postgres into csv or whatever.
    
    while (!feof($file_handle)) {
    
       $line = fgets($file_handle);
       $vals = json_decode($line,true);
       
       	$connectionString = "host=localhost port=5432 dbname=JSON user=username password=somepassword";
    	$dbConnection = pg_connect($connectionString)or die('fail');
    	
    	pg_query("INSERT INTO \"TABLE_1\"(
                \"JSON_TOP_LEVEL_1\" ,
                \"JSON_TOP_LEVEL_1.NEXT_LEVEL\" ,
                \"JSON_TOP_LEVEL_2\" )
           VALUES ('".helperNull($vals['JSON_TOP_LEVEL_1'])."',
                '".helperNull($vals['JSON_TOP_LEVEL_1']['NEXT_LEVEL'])."',
                '".helperNull($vals['JSON_TOP_LEVEL_2'])."');");	
    }
    fclose($file_handle);


    ----------

    I posted the code above. I am not a php programmer nor do I have much experience with JSON at all, but this works.

    My main problem here is that I don't know the structure of the JSON. This process works great to convert the JSON into a table where the structure is easier for me to handle.
     

Share This Page