I'll try to describe the issue again. (It applies not only to expand
method, but also saving the unicode strings in json(b) type via Mojo::Pg)
In this example I'll use next table with text and jsonb columns:
test=> \d t
Table "public.t"
Column | Type | Modifiers
--------+---------+-----------
id | integer |
t | text |
j | jsonb |
test=>
Following script insert data into this table:
use Mojo::Base -strict;
use Mojo::Pg;
use JSON::XS;
my $db = Mojo::Pg->new('postgresql://and@/test')->db;
my $json = JSON::XS->new->ascii(1);
$db->query('insert into t (id, t, j) values (1, ?, ?)', '♥', {json => {'♥'
=> '♥'}});
$db->query('insert into t (id, t, j) values (2, ?, ?)', '♥',
$json->encode({'♥' => '♥'}));
After executing the script, data in the table look like this:
test=> select * from t;
id | t | j
----+---+--------------------------
1 | ♥ | {"â\u0099¥": "â\u0099¥"}
2 | ♥ | {"♥": "♥"}
(2 rows)
test=>
Postgres can accept unicode symbols as sequences of \uXXXX (
http://www.postgresql.org/docs/9.4/static/datatype-json.html). So, while
insert json via Mojo::Pg we need to encode json with only ascii symbols.
Otherwise, the data will be encoded twice.
Upon select the value (that correctly stored in postgres), you get an error
while expand:
use Mojo::Base -strict;
use Mojo::Pg;
my $db = Mojo::Pg->new('postgresql://and@/test')->db;
say $db->query('select * from t where id = 2')->expand->hash->{j}{'♥'};
$ perl t.pl
Input is not UTF-8 encoded at
/home/and/perl5/perlbrew/perls/perl-5.20.0/lib/site_perl/5.20.0/Mojo/Pg/Results.pm
line 51.
$
This is because the scalar already with wide characters and UTF8 flag
(converted by DBD::Pg, I guess):
use Mojo::Base -strict;
use Mojo::Pg;
use Devel::Peek;
my $db = Mojo::Pg->new('postgresql://and@/test')->db;
my $a = $db->query('select * from t where id = 2')->hash->{j};
Dump $a;
$ perl t.pl
SV = PV(0x254c3c0) at 0x15b19f8
REFCNT = 1
FLAGS = (PADMY,POK,IsCOW,pPOK,UTF8)
PV = 0x25796a0 "{\"\342\231\245\": \"\342\231\245\"}"\0 [UTF8
"{"\x{2665}": "\x{2665}"}"]
CUR = 14
LEN = 16
COW_REFCNT = 0
$
So we need encode data in UTF8 and then use decode_json while expand.
On Wed, Jun 17, 2015 at 3:56 PM, sri <[email protected]> wrote:
> is_deeply $db->query('select ?::json as unicode', {json => {'☃' => '♥'}})
>> ->expand->hash, {unicode => {'☃' => '♥'}}, 'right structure';
>> +is_deeply $db->query("select json_build_object('test', ?::text) as j",
>> '♥')
>> + ->expand->hash, {j => {test => '♥'}}, 'right structure';
>>
>
> If one works and the other doesn't, it seems pretty clear to me that you
> must be using the function wrong.
>
> --
> sebastian
>
> --
> You received this message because you are subscribed to the Google Groups
> "Mojolicious" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to [email protected].
> To post to this group, send email to [email protected].
> Visit this group at http://groups.google.com/group/mojolicious.
> For more options, visit https://groups.google.com/d/optout.
>
--
Andrey Khozov
--
You received this message because you are subscribed to the Google Groups
"Mojolicious" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/mojolicious.
For more options, visit https://groups.google.com/d/optout.
