MySQL JSON column loses decimal precision when JSON object inserted as a string literal
As noted in the comments to the question, this appears to be an issue with the way MySQL parses JSON literals containing non-integer numeric values for which DOUBLE is not the best data type. If you want to supply the JSON as a string literal to speed up the batch insert process then one possible workaround would be to INSERT the numeric values as JSON strings and then UPDATE the rows after the batch insert is complete:
mysql> SELECT VERSION();+-------------------------+| VERSION() |+-------------------------+| 5.7.20-0ubuntu0.16.04.1 |+-------------------------+1 row in set (0.01 sec)mysql> CREATE TABLE json_test (id INT PRIMARY KEY, jv JSON);Query OK, 0 rows affected (0.20 sec)Insert the numeric values as strings ...
mysql> INSERT INTO json_test (id, jv) VALUES (1, '{"value": "212765.700000000010000"}');Query OK, 1 row affected (0.11 sec)mysql> INSERT INTO json_test (id, jv) VALUES (2, '{"whatever": "foo"}');Query OK, 1 row affected (0.06 sec)mysql> INSERT INTO json_test (id, jv) VALUES (3, '{"value": "212765.700000000010000", "whatever": "bar"}');Query OK, 1 row affected (0.01 sec)mysql> SELECT * FROM json_test;+----+--------------------------------------------------------+| id | jv |+----+--------------------------------------------------------+| 1 | {"value": "212765.700000000010000"} || 2 | {"whatever": "foo"} || 3 | {"value": "212765.700000000010000", "whatever": "bar"} |+----+--------------------------------------------------------+3 rows in set (0.01 sec)... and then UPDATE the rows to CAST the values to DECIMAL:
mysql> UPDATE json_test SET jv = JSON_REPLACE(jv, '$.value', CAST(JSON_EXTRACT(jv, '$.value') AS DECIMAL(21,15))) WHERE JSON_TYPE(JSON_EXTRACT(jv, '$.value')) = 'STRING';Query OK, 2 rows affected (0.04 sec)Rows matched: 2 Changed: 2 Warnings: 0mysql> SELECT * FROM json_test;+----+------------------------------------------------------+| id | jv |+----+------------------------------------------------------+| 1 | {"value": 212765.700000000010000} || 2 | {"whatever": "foo"} || 3 | {"value": 212765.700000000010000, "whatever": "bar"} |+----+------------------------------------------------------+3 rows in set (0.01 sec)mysql> SELECT JSON_TYPE(JSON_EXTRACT(jv, '$.value')) AS jt FROM json_test WHERE id=1;+---------+| jt |+---------+| DECIMAL |+---------+1 row in set (0.01 sec)