Re: Cast jsonb to numeric, int, float, bool

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