title: perl json output from mysql query
body: <p>I am trying to output mysql query results into JSON format,but struggling to output the array in the format i want.</p>
  
  <p>my sql table is as below</p>
  
  <pre><code>Array1,Type,Somevalue
  Record1,Type1,1
  Record1,Type2,2
  Record1,Type3,3
  Record2,Type1,4
  Record3,Type1,5
  Record3,Type3,6 
  </code></pre>
  
  <p>My perl code to retrive mysql rows and parse into json is below</p>
  
  <pre><code>use DBD::Mysql;
  use JSON::XS;
  use IO::File;
  use warnings;
  use Data::Dumper::Names;
  use POSIX;
  
  
  my $json = JSON::XS-&gt;new-&gt;ascii-&gt;pretty-&gt;allow_nonref;
  
  my $datasource = "dbi:mysql:database=$dbname;host=$host;port=$port";
  #PERL DBI CONNECT
  my $dbh =DBI-&gt;connect($datasource, $dbusername, $dbpasswd) || die "Could not connect to database: $DBI::errstr";
  #PREPARE THE QUERY
  my $query1=qq(select * from table);
  
  my $query_handle1 = $dbh-&gt;prepare($query1);
  
  #EXECUTE THE QUERY
  $query_handle1-&gt;execute();
  
  #my variable
  my $i;
  
  my @Types = ("Type1","Type2","Type3");
  my @Array1 = ("Record1","Record2","Record3","Record4");
  my @sql_query_1_Results;
  
  #LOOP THROUGH RESULTS and store results for later processing
  while(my $row = $query_handle1-&gt;fetchrow_hashref) {
  push @sql_query_1_Results, $row;
  }
  
  # create data arrays for record we should get 3 arrays with values
  for ($i=0;$i&lt;=$#sql_query_1_Results;$i++)
  {
  push @{$sql_query_1_Results[$i]-&gt;{record}},$sql_query_1_Results[$i]-&gt;{Some_Value};
  }
  
  foreach my $record(@Array1){
  $json = encode_json({ 'name' =&gt; $record, 'data'=&gt; \@{$record}  });
  push @chart1_data, $json;
  }
  #DISCONNECT FROM THE DATABASE
  $dbh-&gt;disconnect();
  
  #JSON OUTPUT
  my $chart1_data_json=encode_json( { chart1 =&gt; \@chart1_data } );
  print $chart1_data_json;
  </code></pre>
  
  <p>The above code will give me the array as below</p>
  
  <pre><code>Record1=[1,2,3]
  Record2=[4]
  Record3=[5,6]
  </code></pre>
  
  <p>but i would like to maintain the order of the type i.e in the format below</p>
  
  <pre><code>Record1=[1,2,3]
  Record2=[4,0,0]
  Record3=[5,0,6]
  </code></pre>