Discussion:
How to handle JSON with utf8 from postgres in Mojo::Pg?
(too old to reply)
Andrey Khozov
2015-06-16 09:17:27 UTC
Permalink
When JSON column contains utf8 string Mojo::Pg unable expand this data to
hash. Example:

use Mojo::Pg;
use Mojo::Base -base;

my $db = Mojo::Pg->new->db;

$db->query('create table test (name text)');
$db->query("insert into test (name) values ('♥')");

say $db->query('select name from test')->array->[0]; # ♥
$db->query("select name, json_build_object('value', name) from
test")->expand->hashes; # exception "Input is not UTF-8 encoded"

I don't need to use expand in this case?
--
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 mojolicious+***@googlegroups.com.
To post to this group, send email to ***@googlegroups.com.
Visit this group at http://groups.google.com/group/mojolicious.
For more options, visit https://groups.google.com/d/optout.
sri
2015-06-16 11:40:57 UTC
Permalink
Post by Andrey Khozov
When JSON column contains utf8 string Mojo::Pg unable expand this data to
hash.
This is false.


https://github.com/kraih/mojo-pg/commit/6d6d6c3f4525b0ed044369a4facdefc422ddd1c8

--
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 mojolicious+***@googlegroups.com.
To post to this group, send email to ***@googlegroups.com.
Visit this group at http://groups.google.com/group/mojolicious.
For more options, visit https://groups.google.com/d/optout.
Andrey Khozov
2015-06-17 06:17:36 UTC
Permalink
Hmm, ok, this example works fine, but how about this:

diff --git a/t/database.t b/t/database.t
index b4c4d7b..908b781 100644
--- a/t/database.t
+++ b/t/database.t
@@ -148,6 +148,8 @@ is_deeply $results->expand->array, [undef], 'right
structure';
is_deeply $results->expand->array, undef, 'no more results';
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';

# Fork-safety
$dbh = $pg->db->dbh;
Post by Andrey Khozov
When JSON column contains utf8 string Mojo::Pg unable expand this data to
hash.
This is false.
https://github.com/kraih/mojo-pg/commit/6d6d6c3f4525b0ed044369a4facdefc422ddd1c8
--
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
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 mojolicious+***@googlegroups.com.
To post to this group, send email to ***@googlegroups.com.
Visit this group at http://groups.google.com/group/mojolicious.
For more options, visit https://groups.google.com/d/optout.
sri
2015-06-17 10:56:37 UTC
Permalink
Post by Andrey Khozov
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 mojolicious+***@googlegroups.com.
To post to this group, send email to ***@googlegroups.com.
Visit this group at http://groups.google.com/group/mojolicious.
For more options, visit https://groups.google.com/d/optout.
Andrey Khozov
2015-06-17 15:33:06 UTC
Permalink
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.
Post by Andrey Khozov
is_deeply $db->query('select ?::json as unicode', {json => {'☃' => '♥'}})
Post by Andrey Khozov
->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
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 mojolicious+***@googlegroups.com.
To post to this group, send email to ***@googlegroups.com.
Visit this group at http://groups.google.com/group/mojolicious.
For more options, visit https://groups.google.com/d/optout.
sri
2015-06-17 16:10:15 UTC
Permalink
Post by Andrey Khozov
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.
This is not quite correct, but i do understand now what you were trying to
tell me.


https://github.com/kraih/mojo-pg/commit/6dcc2a760634bfffc3a543af7e13ca1b6f2811bd

--
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 mojolicious+***@googlegroups.com.
To post to this group, send email to ***@googlegroups.com.
Visit this group at http://groups.google.com/group/mojolicious.
For more options, visit https://groups.google.com/d/optout.
Андрей Хозов
2015-06-17 18:46:50 UTC
Permalink
It works, thanks!
Post by Andrey Khozov
Postgres can accept unicode symbols as sequences of \uXXXX (
Post by Andrey Khozov
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.
This is not quite correct, but i do understand now what you were trying to
tell me.
https://github.com/kraih/mojo-pg/commit/6dcc2a760634bfffc3a543af7e13ca1b6f2811bd
--
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
Visit this group at http://groups.google.com/group/mojolicious.
For more options, visit https://groups.google.com/d/optout.
--
АМЎрей ХПзПв
--
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 mojolicious+***@googlegroups.com.
To post to this group, send email to ***@googlegroups.com.
Visit this group at http://groups.google.com/group/mojolicious.
For more options, visit https://groups.google.com/d/optout.
Loading...