MySQL JSON column loses decimal precision when JSON object inserted as a string literal

Question

I am trying to insert a JSON document into a MySQL JSON column and have noticed that decimal precision is lost in.

{"value": 212765.700000000010000}

Gets reduced to

{"value": 212765.7}

I've tried inserting directly via MySQL Workbench and I've noticed different behavior depending on how I do. For example:

insert into json_test values ('{"value": 212765.700000000010000}');

Produces the same result.. however the following works:

insert into json_test values (json_object('value', 212765.700000000010000));

I can generate the insert statement dynamically building the JSON object with the json_* function calls which works for single inserts.. but I'm using JDBC batch updates which requires the SQL to have a consistent number of parameters (our JSON documents vary in structure, so this does not work) or to explicitly set the values without using bind parameters which is obviously a security risk.

Any way around this?

Answer 1

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: 0

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)

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)