perl json output from mysql query

Question

I am trying to output mysql query results into JSON format,but struggling to output the array in the format i want.

my sql table is as below

Array1,Type,Somevalue
Record1,Type1,1
Record1,Type2,2
Record1,Type3,3
Record2,Type1,4
Record3,Type1,5
Record3,Type3,6 

My perl code to retrive mysql rows and parse into json is below

use DBD::Mysql;
use JSON::XS;
use IO::File;
use warnings;
use Data::Dumper::Names;
use POSIX;


my $json = JSON::XS->new->ascii->pretty->allow_nonref;

my $datasource = "dbi:mysql:database=$dbname;host=$host;port=$port";
#PERL DBI CONNECT
my $dbh =DBI->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->prepare($query1);

#EXECUTE THE QUERY
$query_handle1->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->fetchrow_hashref) {
push @sql_query_1_Results, $row;
}

# create data arrays for record we should get 3 arrays with values
for ($i=0;$i<=$#sql_query_1_Results;$i++)
{
push @{$sql_query_1_Results[$i]->{record}},$sql_query_1_Results[$i]->{Some_Value};
}

foreach my $record(@Array1){
$json = encode_json({ 'name' => $record, 'data'=> \@{$record}  });
push @chart1_data, $json;
}
#DISCONNECT FROM THE DATABASE
$dbh->disconnect();

#JSON OUTPUT
my $chart1_data_json=encode_json( { chart1 => \@chart1_data } );
print $chart1_data_json;

The above code will give me the array as below

Record1=[1,2,3]
Record2=[4]
Record3=[5,6]

but i would like to maintain the order of the type i.e in the format below

Record1=[1,2,3]
Record2=[4,0,0]
Record3=[5,0,6]

Answer 1

#!/usr/bin/perl

use strict ;
use warnings;
use DBI;
use JSON::XS;
use IO::File;
use Data::Dumper::Names;
use POSIX;

my $json = JSON::XS->new->ascii->pretty->allow_nonref;

my ( $dbname , $host , $port , $dbusername , $dbpasswd ) ; # Satisfy strict

my $datasource = "dbi:mysql:database=$dbname;host=$host;port=$port";
#PERL DBI CONNECT
my $dbh =DBI->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->prepare($query1);

#EXECUTE THE QUERY
$query_handle1->execute();

my @Types = ("Type1","Type2","Type3");
my @Array1 = ("Record1","Record2","Record3","Record4");

# BEGIN modified data munching
my %result ;
my %Type_Pos ;
@Type_Pos{@Types} = ( 0 .. $#Types ) ; # Map the type to position

while( my $row = $query_handle1->fetchrow_hashref ) {
  if( ! exists $result{$row->{Array1}} ) {
    # Initialize Record with all possible types
    $result{$row->{Array1}} = [ map { 0 } @Types ] ;
  }
  $result{$row->{Array1}}->[$Type_Pos{$row->{Type}}] = $row->{Somevalue} ;
}

my @chart1_data = map { encode_json( { 'name' => $_, 'data'=> $result{$_} } )
                      } sort keys %result ;
# END modified data munching

$dbh->disconnect();

#JSON OUTPUT
my $chart1_data_json=encode_json( { chart1 => \@chart1_data } );
print $chart1_data_json;

This should do what you asked.
Instead of saving the data in an array I contruct a hash where the record is the key and an arrayref of Somevalue (in order of types) is the value.