Re: Cast jsonb to numeric, int, float, bool - Mailing list pgsql-hackers

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