0

I can't get this Perl code to return true integer values for integers in the table. The MySQL table columns are correctly specified as integers, yet the JSON output here wraps all query values in quotes. How can I correctly preserve data-types (esp. integers and boolean values) as specified?

use strict;
use warnings;
use DBI;
use JSON;

my $sth = "SELECT id, name, age FROM table";

my $data = $dbh->selectall_arrayref($sth, {Slice => {}});

my $response = encode_json($data);
print $response;

## outputs: {"id":"1","name":"Joe Blodge","age":"42"}

What am I doing wrong here? How can I get this to output the correctly formatted JSON:

{"id":1,"name":"Joe Blodge","age":42}
Colin R. Turner
  • 1,323
  • 15
  • 24
  • 4
    Read the section on **PERL -> JSON** in the `JSON` documentation. Using `Cpanel::JSON::XS` might give better results, too. – Shawn Oct 27 '18 at 16:57
  • 2
    Also check https://stackoverflow.com/questions/40800205/why-does-dbi-implicitly-change-integers-to-strings – UjinT34 Oct 27 '18 at 16:59

1 Answers1

3

DBD::mysql returns all results as strings (see https://github.com/perl5-dbi/DBD-mysql/issues/253). Normally Perl doesn't care, encoding to JSON is one of the few times when it matters. You can either use Cpanel::JSON::XS::Type to provide type declarations for your JSON structure:

use Cpanel::JSON::XS;
use Cpanel::JSON::XS::Type;

my $response = encode_json($data, {id => JSON_TYPE_INT, name => JSON_TYPE_STRING, age => JSON_TYPE_INT});

or you can go through and numify the appropriate elements before JSON encoding.

$data->{$_} += 0 for qw(id age);

It is possible to check the type (as indicated by MySQL) of each returned column, if you construct and execute your query using a statement handle then the type will be available as an array in $sth->{TYPE}, but this is pretty complex and may not be reliable.

Grinnz
  • 9,093
  • 11
  • 18
  • Excellent thanks. It's looking debatable whether constructing the entire JSON output 'manually' might actually be more efficient. ie `$name =~ s/"/\"/g; my $json = qq~{"id":$id, "name":"$name", "age": $age}~;` – Colin R. Turner Oct 28 '18 at 11:15
  • 2
    That's not a good idea. JSON encoding is not as simple as you may think especially if one of your names ends up having a non-ASCII character. Cpanel::JSON::XS is one of the fastest and most correct JSON encoders available, anyway. – Grinnz Oct 28 '18 at 18:56