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?

Check if you got a NULL value (control flow functions), and if so use one of the functions that _create_ JSON ...? https://dev.mysql.com/doc/refman/5.7/en/json-creation-functions.html — CBroe, Nov 30 '16 at 20:05
Try: `UPDATE \`testjson\` SET \`extra\` = IF(\`extra\` IS NULL, JSON_OBJECT('age', 87), JSON_INSERT(\`extra\`, '$.age', 87));`. — wchiquito, Dec 01 '16 at 09:28

score 35 · Accepted Answer · answered Feb 07 '17 at 21:57

35

Use COALESCE:

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

answered Feb 07 '17 at 21:57

skedastik

654
7
14

1

very nice! Thanks! – Reed Mar 15 '17 at 17:22
just an admonition, I ran into the fact my fields weren't `null` in the mysql-way, but `null` in JSON-way, i.e. the field content was stringly `"null"` therefore that COALESCE didn't work outta the box for me. – Adrian Föder Aug 22 '17 at 07:27
1

follow-up: I found no other way than `… SET extra = JSON_SET(IF(JSON_TYPE(extra) = 'NULL', "{}", extra), "$.age", 87)` (adapted to your sample ofc) – Adrian Föder Aug 22 '17 at 08:02
1

@AdrianFöder it should be `SET extra = JSON_SET(IF(JSON_TYPE(extra) is NULL, "{}", extra), "$.age", 87)` – proprius Mar 20 '18 at 12:55

score 4 · Answer 2 · edited Mar 09 '20 at 11:05

4

SET extra = JSON_SET(IFNULL(extra, "{}"), "$.age", 87)

edited Mar 09 '20 at 11:05

Ramin eghbalian

2,348
1
16
36

answered Mar 09 '20 at 09:46

Larry Poort

56
3

Love this solution, super simple :) – Justin F Jul 21 '20 at 20:12

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

2 Answers2