Flatten Complex JSON

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

  1. MagnumOP macrumors regular


    Jul 5, 2010
    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?
    2. A tool that can flatten the JSON into CSV or some other 'flat' data structure?
  2. MagnumOP thread starter macrumors regular


    Jul 5, 2010
    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

    Aug 21, 2013
    San Francisco
    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


    Jul 5, 2010
    //function to implode nested arrays
    function multiImplode($array) {
        $ret = '';
    	$glue = ', ';
    	    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'])."',


    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