How to handle JSON with utf8 from postgres in Mojo::Pg?

[Andrey Khozov]

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

[sri]

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

[Andrey Khozov]

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;

--
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 mojol...@googlegroups.com.
Visit this group at http://groups.google.com/group/mojolicious.
For more options, visit https://groups.google.com/d/optout.

--

Andrey Khozov

[sri]

 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 

[Andrey Khozov]

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.

--

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 mojol...@googlegroups.com.
Visit this group at http://groups.google.com/group/mojolicious.
For more options, visit https://groups.google.com/d/optout.

--

Andrey Khozov

[sri]

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 

[Андрей Хозов]

It works, thanks!

--

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 mojol...@googlegroups.com.
Visit this group at http://groups.google.com/group/mojolicious.
For more options, visit https://groups.google.com/d/optout.

--

Андрей Хозов