CODEWITHSUNDEEP
mysqljson
DEV
DEV

Reputation: 2176

MySQL 5.7 JSON column update

I am using MySQL 5.7. I have a table with a JSON column.

MySQL [test_db]> select * from mytable;
+----+-------+---------------------+
| id | name  | hobby               |
+----+-------+---------------------+
|  1 | Rahul | {"Game": "Cricket"} |
|  2 | Sam   | null                |
+----+-------+---------------------+

Here, for row id = 2, I want to insert a data. I did -

update mytable set hobby = JSON_SET(hobby, '$.Game', 'soccer') where id = 2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

It seems like data inserted properly, But when I checked 

MySQL [test_db]> select * from mytable;
+----+-------+---------------------+
| id | name  | hobby               |
+----+-------+---------------------+
|  1 | Rahul | {"Game": "Cricket"} |
|  2 | Sam   | null                |
+----+-------+---------------------+

data is not inserted, Can anybody give some hint, what I am missing here.

Thanks.

Upvotes: 1

Views: 210

Answers (1)

Blue
Blue

Reputation: 22911

Hobby is NULL, and you can't set a property on NULL, so use an IF statement instead, to convert null to an empty object first (Or initialize hobby as an empty object instead of NULL):

UPDATE mytable
SET hobby = JSON_SET(IF(hobby IS NULL, '{}', hobby), '$.Game', 'soccer')
WHERE id = 2;

Alternatitvely, use COALESCE:

UPDATE mytable
SET hobby = JSON_SET(COALESCE(hobby, '{}'), '$.Game', 'soccer')
WHERE id = 2;

See dbfiddle here.

Upvotes: 1

Related Questions