mysql JSON_SET can't insert into column with NULL value(5.7+)

Question

I am exploring the JSON funcions of newer mysql server. But run into a very rudimentary issue.

How do I insert {"key":"value"} into a JSON column when I don't know if the current value is NULL or not?

The table is for illustration only:

CREATE TABLE `testjson` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `extra` JSON NULL DEFAULT NULL,
    PRIMARY KEY (`id`)
)

Test data with NULL and valid json value.

| id | extra             | 
| -: | -                 | 
| 1  | (NULL)            | 
| 2  | {"name": "james"} | 

Desired outcome:

| id | extra                        | 
| -: | -                            | 
| 1  | {"age": 87}                  | 
| 2  | {"age": 87, "name": "james"} | 

Now, I try to add {"age":87} into all rows:

UPDATE testjson
    SET extra = JSON_SET(extra,'$.age', 87)
;

UPDATE testjson
    SET extra = JSON_MERGE(extra,JSON_OBJECT('age', 87)),
;

None of above updates the NULL field. Tried to set the column default to {}, but it is not allowed.

Because NULL is not valid JSON data, none of the mysql JSON funcion works: https://dev.mysql.com/doc/refman/5.7/en/json-function-reference.html

My current work around is to set NULL to {} before insert, but it is stupid. An simple update should not use two queries.

How do you guys handle this?

Answer 1

Use COALESCE:

UPDATE testjson SET extra = JSON_SET(COALESCE(extra, '{}'), '$.age', 87);