macOS Flatten Complex JSON

[MagnumOP]

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?

 

[MagnumOP]

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.

 

[MeUnix]

If anyone is interested in the code I would be glad to post it.

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.

 

[MagnumOP]

//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'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.

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.