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
