Today, I have learnt that Snowflake will convert decimal values to integer values if all trailings are zeros. Please see below examples:
SELECT 1000.0, typeof(1000.0), 1000.1, typeof(1000.1);
It returns:
1000 INTEGER 1000.1 DECIMAL
The same applies to PARSE_JSON:
SELECT PARSE_JSON( '{"key": 1000.0}' );
It returns:
{ "key": 1000 }
Instead of:
{ "key": 1000.0 }
After researching with our team, it turned out that it was expected behaviour. In fact, Snowflake has been behaving this way for a long time. However, for the PARSE_JSON, it was recently updated for consistency. It was announced at 4.39 Release Notes back in November 2020. Please refer to section under:
JSON Data: Changes to the Handling of Numeric Values with Trailing Zeros
I have compared with MySQL and PostgreSQL, they however, preserve the decimal type in above scenarios. See my test below:
MySQL:
mysql> select 1000.0; +--------+ | 1000.0 | +--------+ | 1000.0 | +--------+ mysql> SELECT JSON_EXTRACT('{"name": 1000.0}', '$.name'); +--------------------------------------------+ | JSON_EXTRACT('{"name": 1000.0}', '$.name') | +--------------------------------------------+ | 1000.0 | +--------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT JSON_TYPE('1000.0'); +---------------------+ | JSON_TYPE('1000.0') | +---------------------+ | DOUBLE | +---------------------+ 1 row in set (0.00 sec) mysql> SELECT JSON_TYPE('1000'); +-------------------+ | JSON_TYPE('1000') | +-------------------+ | INTEGER | +-------------------+ 1 row in set (0.00 sec)
PostgreSQL
postgres=# select 1000.0; ?column? ---------- 1000.0 (1 row) postgres=# select to_json('{"why are we loosing the decimal ?": 1000.0}'::text); to_json -------------------------------------------------- "{\"why are we loosing the decimal ?\": 1000.0}" (1 row)
So look out for those differences.