Working with JSON data

Matthew Bates commented on Jun 18, 2019

I am working with data being pulled from a MySQL database. One of my fields is JSON. The JSON data consists of four fields and is a variable number of records. The field is a list of characteristics of hard drives and is in the following format: [{"crushed": "Yes", "diskmfg": "IBM", "degaussed": "Yes", "diskserial": "erweq55645645"}] If there is more than 1 disk recorded in the database: [{"crushed": "Yes", "diskmfg": "IBM", "degaussed": "Yes", "diskserial": "erweq55645645"}, {"crushed": "Yes", "diskmfg": "IBM", "degaussed": "Yes", "diskserial": "asdfsdfsdfsadf"}, {"crushed": "No", "diskmfg": "Seagate", "degaussed": "No", "diskserial": "sdfasdfasdfsdfsd"}, {"crushed": "Yes", "diskmfg": "Seagate", "degaussed": "Yes", "diskserial": "asdfsdfsdfewwww"}] I read a previous post regarding processing of JSON data and saw the suggestion to use the Map function. I entered the following code in my build php, but am unsure of how to make the data appear in the view php. Also, how do I handle determining how many records are contained in the JSON data since it will be variable? Code in my build.php is below:

$this->src('servers')
      ->query("SELECT * FROM Decom where equiptype=\"Server\"")
       ->pipe(new Map([
        "{test}"=> function($row) {
           $jsonData = $row["drives"];
           $jsonData = json_decode($jsonData, true);
           $row["crushed"] = $jsonData["crushed"];
           $row["diskmfg"] = $jsonData["diskmfg"];
           $row["degaussed"] = $jsonData["degaussed"];
           $row["diskserial"] = $jsonData["diskserial"];
        return $row;
        }
      ]))
      ->pipe($this->dataStore('server_data'));

David Winterburn commented on Jun 19, 2019

Hi Matthew,

The Map process piping should be:

	->pipe(new Map([
        "{value}"=> function($row) {
            $rows = [];
			$jsonData = $row["drives"];
			$drives = json_decode($jsonData, true);
			foreach ($drives as $drive) {
				$oneRow = $row;
				$oneRow["crushed"] = $drive["crushed"];
				$oneRow["diskmfg"] = $drive["diskmfg"];
				$oneRow["degaussed"] = $drive["degaussed"];
				$oneRow["diskserial"] = $drive["diskserial"];
				array_push($rows, $oneRow);
			}
			return $rows;
        }
    ]))

If there're multiple drives in the "drive" JSON field we shall return multiple rows. Each of those rows has the same data as the original row but adds drive data fields ("crushed", "diskmfg", etc) decoded from "drives" field. From there you could manipulate the drives data and show them in the view file with tables, charts, etc.

Let us know if you have other questions. Thanks!

Matthew Bates commented on Jun 19, 2019

Thanks David, I ended up just using the "formatValue" feature in my view file instead of using the Map function

"drives"=>array(
    "label"=>"Drives",
    "formatValue"=>function($value,$row){
     $json_data=$value;
     $drives = json_decode($json_data,true);
     $lastdrive = count($drives)-1;
     for ($i=0;$i<=$lastdrive;$i++){
       $diskmfg = $drives[$i]["diskmfg"];
       $diskserial = $drives[$i]["diskserial"];
       $degaussed = $drives[$i]["degaussed"];
       $crushed = $drives[$i]["crushed"];
       $diskno = $i+1;
       $output .="$diskno: Manufacturer: $diskmfg|Serial: $diskserial|Degaussed: $degaussed|Crushed: $crushed<br>";
     }
   return $output;
   }
    ),

KoolReport's Forum

Working with JSON data #933

Build Your Excellent Data Report