| CC: | lembark [...] wrkhors.com |
| Subject: | Fetchall arrayref of ( serial, jsonb ) query result gives text scalar '( id_value, "{jsonb contents}")' rather than array with two elements. |
| Date: | Sun, 27 Sep 2015 18:15:41 -0500 |
| To: | bug-DBD-Pg [...] rt.cpan.org |
| From: | Steven Lembark <lembark [...] wrkhors.com> |
What seems odd is that I'm getting a single scalar that looks like
JSON of the result rather than an arrayref with two elements.
PG 9.4.4 (AWS RDS instance).
Using DBD::Pg 3.5.1,
Perl 5.22.0 (compiled locally).
Setup:
create table address
(
address_id serial not null,
name varchar(32) not null,
data jsonb not null,
primary key ( address_id ),
unique ( name )
);
Prepare:
select address, address_data
from address
where name = ?
Instead of getting an arrayref with two elements I get a single
element with the id and json as a list:
243: $dbh->prepare( $sql => $fixed_argz )
DB<2> p $sql
select
(
address_id,
address
)
from active_address
where lookup = $1
DB<3> x $fixed_argz;
0 HASH(0x2e20810)
'pg_placeholder_dollaronly' => 1
'pg_placeholder_nocolons' => 1
eval
{
'ARRAY' eq reftype $_[0]
? $sth->execute( @{ $_[0] } )
: $sth->execute( @_ )
};
croak "Failed execute_query: $@"
if $@;
$sth->{ Active }
or return;
my $flat = wantarray
// do
{
$sth->finish;
return;
};
$flat
? @{ $sth->fetchall_arrayref }
: $sth->fetchall_arrayref
list context return from SoRipe::Util::Database::execute_query:
0 ARRAY(0x3cfe3e8)
0 '(7002,"{""zip"": ""63110-3704"", ""city"": ""St Louis"", ""state"": ""MO"", ""street"": ""3646 Flora Pl"", ""address"": ""3646 Flora Pl St Louis MO 63110-3704"", ""quality"": ""P1AAA"", ""is_valid"": 1, ""latitude"": 38.612407, ""longitude"": -90.242071, ""location_id"": 799}")'
i.e., the single scalar:
'(7002,"{""zip"": ""63110-3704"", ""city"": ""St Louis"", ""state"": ""MO"", ""street"": ""3646 Flora Pl"", ""address"": ""3646 Flora Pl St Louis MO 63110-3704"", ""quality"": ""P1AAA"", ""is_valid"": 1, ""latitude"": 38.612407, ""longitude"": -90.242071, ""location_id"": 799}")'
rather than
[
"7002",
"{""zip"": ""63110-3704"", ""city"": ""St Louis"", ""state"": ""MO"", ""street"": ""3646 Flora Pl"", ""address"": ""3646 Flora Pl St Louis MO 63110-3704"", ""quality"": ""P1AAA"", ""is_valid"": 1, ""latitude"": 38.612407, ""longitude"": -90.242071, ""location_id"": 799}"
]
Sorry if this is expected behavior, but I didn't see anything in the
docs about special handling of jsonb data.
thanks
--
Steven Lembark 3646 Flora Pl
Workhorse Computing St Louis, MO 63110
lembark@wrkhors.com +1 888 359 3508