I have a MySQL table with a JSON column. I want to update some rows in the JSON column to change a json value from a float to an integer. e.g {"a": 20.0} should become {"a": 20}. It looks like MySQL finds these 2 values equivalent, so it never bothers to update the row.
Here is the state of my test table:
mysql> describe test;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| val | json | YES | | NULL | |
+-------+------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> select * from test;
+----+-------------+
| id | val |
+----+-------------+
| 1 | {"a": 20.0} |
+----+-------------+
1 row in set (0.00 sec)
My aim is to change val to {"a": 20}
I've tried the following queries:
mysql> update test set val=JSON_OBJECT("a", 20) where id=1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
(0 rows changed)
mysql> update test
set val=JSON_SET(
val,
"$.a",
FLOOR(
JSON_EXTRACT(val, "$.a")
)
)
where id=1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
(0 rows changed)
mysql> insert into test (id, val) values (1, JSON_OBJECT("a", 20)) ON DUPLICATE KEY UPDATE id=VALUES(id), val=VALUES(val);
Query OK, 0 rows affected, 2 warnings (0.00 sec)
(0 rows affected)
It looks like it doesn't matter how I try to write it, whether I attempt to modify the existing value, or specify a whole new JSON_OBJECT. So I'm wondering if the reason is simply that MySQL considers the before & after values to be equivalent.
Is there any way around this?