MySQL: Cannot update JSON column to convert value from float to integer

Question

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?

user_1766831 · Answer

(This does not address the original Question, but addresses a problem encountered in Answering it.)

Gross... 8.0 has a naughty history of all-too-quickly removing something after recently deprecating it. Beware. Here is the issue with VALUES from the Changelog for 8.0.20:

----- 2020-04-27 8.0.20 General Availability -- -- -----

The use of VALUES() to access new row values in INSERT ... ON DUPLICATE KEY UPDATE statements is now deprecated, and is subject to removal in a future MySQL release. Instead, you should use aliases for the new row and its columns as implemented in MySQL 8.0.19 and later.

For example, the statement shown here uses VALUES() to access new row values:

INSERT INTO t1 (a,b,c) VALUES (1,2,3),(4,5,6)
  ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

Henceforth, you should instead use a statement similar to the following, which uses an alias for the new row:

INSERT INTO t1 (a,b,c) VALUES (1,2,3),(4,5,6) AS new
  ON DUPLICATE KEY UPDATE c = new.a+new.b;

Alternatively, you can employ aliases for both the new row and each of its columns, as shown here:

INSERT INTO t1 (a,b,c) VALUES (1,2,3),(4,5,6) AS new(m,n,p)
  ON DUPLICATE KEY UPDATE c = m+n;

For more information and examples, see INSERT ... ON DUPLICATE KEY UPDATE Statement.

MySQL: Cannot update JSON column to convert value from float to integer

1 Answers