Why can't I properly encode a boolean from PostgreSQL via JSON::XS via Perl?

Question

I have a query on a PostgreSQL system returning a boolean:

my $sth = $dbh->prepare("select 'f'::boolean");
$sth->execute;
my @vals = $sth->fetchrow_array;

According to the DBD::Pg docs,

The current implementation of PostgreSQL returns 't' for true and 'f' for false. From the Perl point of view, this is a rather unfortunate choice. DBD::Pg therefore translates the result for the BOOL data type in a Perlish manner: 'f' becomes the number 0 and 't' becomes the number 1. This way the application does not have to check the database-specific returned values for the data-type BOOL because Perl treats 0 as false and 1 as true. You may set the pg_bool_tf attribute to a true value to change the values back to 't' and 'f' if you wish.

So, that statement should return a 0, which it does, so long as pg_bool_tf returns 0, which it does. However, somewhere along the way JSON::XS (and plain JSON) interprets the returned 0 as a string:

use JSON::XS qw(encode_json);
my $options =
{
    layout => 0,
    show_widget_help => $vals[0] // 1,
};
die encode_json($options);

...dies with:

{"layout":0,"show_widget_help":"0"}

...which would be fine, except that my JavaScript is expecting a boolean there, and the non-empty string "0" gets evaluated to true. Why is the latter 0 quoted and the former not?

According to the JSON::XS docs, this is a main feature:

round-trip integrity

When you serialise a perl data structure using only data types supported by JSON, the deserialised data structure is identical on the Perl level. (e.g. the string "2.0" doesn't suddenly become "2" just because it looks like a number). There minor are exceptions to this, read the MAPPING section below to learn about those.

...which says:

Simple Perl scalars (any scalar that is not a reference) are the most difficult objects to encode: JSON::XS will encode undefined scalars as JSON null values, scalars that have last been used in a string context before encoding as JSON strings, and anything else as number value.

But I never use @vals[0] in a string context. Maybe DBD::Pg uses its boolean 0 as a string somewhere before returning it?

Answer 1

The JSON::XS doc says the following will be converted to true/false

• references to the integers 0 and 1, ie. \0 and \1
• JSON::XS::true and JSON::XS::false

Using one of these should solve your problem