Subject: Re: Cast jsonb to numeric, int, float, bool
To: =?UTF-8?Q?Darafei_=22Kom=d1=8fpa=22_Praliaskouski?= <me@komzpa.net>
Cc: Anastasia Lubennikova <a.lubennikova@postgrespro.ru>,
 pgsql-hackers@lists.postgresql.org
References: <0154d35a-24ae-f063-5273-9ffcdf1c7f2e@postgrespro.ru>
 <9c013081-5dc0-1b87-a559-a0c2d405727a@postgrespro.ru>
 <151985418868.6915.17223208131889314092.pgcf@coridan.postgresql.org>
 <76b000ae-94d1-cd8f-a2e0-e536096469b0@postgrespro.ru>
 <CAC8Q8t+LDe9XMohGVnhNE3GgFhsVMJ38JvEVNf1DG1tg=mGD5Q@mail.gmail.com>
From: Nikita Glukhov <n.gluhov@postgrespro.ru>
Organization: Postgres Professional
Message-ID: <af9cb89f-390e-9dce-74cf-f50371116847@postgrespro.ru>
Date: Thu, 1 Mar 2018 13:20:29 +0300
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101
 Thunderbird/52.6.0
MIME-Version: 1.0
In-Reply-To: 
 <CAC8Q8t+LDe9XMohGVnhNE3GgFhsVMJ38JvEVNf1DG1tg=mGD5Q@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Content-Language: en-US
Precedence: bulk

On 01.03.2018 11:19, Darafei "Komяpa" Praliaskouski wrote:

> > Attached new version of the patch in which I removed duplicated code 
> using new subroutine JsonbExtractScalar(). I am not sure what is 
> better to do when a JSON item has an unexpected type: to throw an  
> error or to return SQL NULL. Also JSON nulls could be converted to SQL 
> NULLs.
>
> I would expect it to follow whatever is happening in JavaScript.
> I'm unsure about mapping of NULL and undefined/null though.
>
> > I should note here that expression (jb -> 'key')::datatype can be 
> rewritten with SQL/JSON function JSON_VALUE: JSON_VALUE(jb, '$.key' 
> RETURNING datatype ERROR ON ERROR)
>
> I would expect some casts to be implicit, so that chaining with other 
> functions is possible:
>
> select ST_MakePoint(r->'lon', r->'lat');
>
> select sum(r->'income');
>
> > But by standard JSON_VALUE tries to cast string JSON items to the 
> specified datatype too, so JSON_VALUE('{"key": "123"}'::jsonb, '$.key' 
> RETURNING int ERROR ON ERROR) does not throw an error but returns 123.
>
> In actual JSON implementations number datatype is usually the one 
> available in browsers, double precision.
> For some numbers (I've met this with nanoseconds) it leads to value 
> being changed on subsequent serializations and deserializations, so 
> it's common to wrap them in a string to be unchanged.
> So, I would expect that to work, but give me an exception if the 
> datatype loses precision on conversion of specific value.

I think that only cast to a numeric type can be made implicit, because 
it does not lose precision.
So, sum(jsonb) will work, but ST_MakePoint(float8, float8) still will 
require an explicit cast.

It seems that in JavaScript we can implicitly cast strings to numerics 
and unwrap one-element arrays.
Examples from Chrome:

> "123.45" / 3
41.15

> "1e100" / 3
3.333333333333333e+99

> "1e1000" / 3
Infinity

> "foo" / 3
NaN

> [123.45] / 3
41.15

> ["123.45"] / 3
41.15

> [123.45, 345] / 3
NaN

> undefined / 3
NaN

But null is converted to 0:

> null / 3
0

> null + 3
3


Below are examples showing how it works with new casts and JSON_VALUE:

=# SELECT '1234567890.1234567890'::jsonb::int2;
ERROR:  cannot cast type jsonb to smallint
LINE 1: SELECT '1234567890.1234567890'::jsonb::int2;
                                              ^

=# SELECT '1234567890.1234567890'::jsonb::int4;
     int4
------------
  1234567890
(1 row)

=# SELECT '1234567890.1234567890'::jsonb::float4;
ERROR:  cannot cast type jsonb to real
LINE 1: SELECT '1234567890.1234567890'::jsonb::float4;
                                              ^

=# SELECT '1234567890.1234567890'::jsonb::float8;
       float8
------------------
  1234567890.12346
(1 row)

=# SELECT '1234567890.1234567890'::jsonb::numeric;
         numeric
-----------------------
  1234567890.1234567890
(1 row)

=# SELECT '"1234567890.1234567890"'::jsonb::numeric;
ERROR:  jsonb value must be numeric

=# SELECT 'null'::jsonb::numeric;
ERROR:  jsonb value must be numeric


=# SELECT JSON_VALUE('1234567890.1234567890', '$' RETURNING int2 ERROR ON ERROR);
ERROR:  smallint out of range

=# SELECT JSON_VALUE('1234567890.1234567890', '$' RETURNING int4 ERROR ON ERROR);
  json_value
------------
  1234567890
(1 row)

=# SELECT JSON_VALUE('1234567890.1234567890', '$' RETURNING float4 ERROR ON ERROR);
  json_value
-------------
  1.23457e+09
(1 row)

=# SELECT JSON_VALUE('1234567890.1234567890', '$' RETURNING float8 ERROR ON ERROR);
     json_value
------------------
  1234567890.12346
(1 row)

=# SELECT JSON_VALUE('1234567890.1234567890', '$' RETURNING numeric ERROR ON ERROR);
       json_value
-----------------------
  1234567890.1234567890
(1 row)


=# SELECT JSON_VALUE('"1234567890.1234567890"', '$' RETURNING int2 ERROR ON ERROR);
ERROR:  value "1234567890.1234567890" is out of range for type smallint

=# SELECT JSON_VALUE('"1234567890.1234567890"', '$' RETURNING int4 ERROR ON ERROR);
ERROR:  invalid input syntax for integer: "1234567890.1234567890"

=# SELECT JSON_VALUE('"1234567890.1234567890"', '$' RETURNING float4 ERROR ON ERROR);
  json_value
-------------
  1.23457e+09
(1 row)

=# SELECT JSON_VALUE('"1234567890.1234567890"', '$' RETURNING float8 ERROR ON ERROR);
     json_value
------------------
  1234567890.12346
(1 row)

=# SELECT JSON_VALUE('"1234567890.1234567890"', '$' RETURNING numeric ERROR ON ERROR);
       json_value
-----------------------
  1234567890.1234567890
(1 row)

=# SELECT JSON_VALUE('"foo"', '$' RETURNING numeric ERROR ON ERROR);
ERROR:  invalid input syntax for type numeric: "foo"


=# SELECT JSON_VALUE('null', '$' RETURNING numeric ERROR ON ERROR);
  json_value
------------

(1 row)

=# SELECT JSON_VALUE('{}', '$' RETURNING numeric ERROR ON ERROR);
ERROR:  SQL/JSON scalar required

=# SELECT JSON_VALUE('[]', '$' RETURNING numeric ERROR ON ERROR);
ERROR:  SQL/JSON scalar required


--

Nikita Glukhov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company