MySQL 8 Cookbook

Modifying

You can modify the data using three different functions: JSON_SET(), JSON_INSERT(), JSON_REPLACE(). Before MySQL 8, we needed a full update of the entire column, which is not the optimal way:

• JSON_SET: Replaces existing values and adds non-existing values. Suppose you want to replace the pin code of the employee and also add details of a nickname:

mysql> UPDATE     emp_details SET     details = JSON_SET(details, "$.address.pin", "560100", "$.nickname", "kai")WHERE     emp_no = 1;Query OK, 1 row affected (0.03 sec)Rows matched: 1 Changed: 1 Warnings: 0

• JSON_INSERT(): Inserts values without replacing existing values

Suppose you want to add a new column without updating the existing values; you can use JSON_INSERT():

mysql> UPDATE emp_details ...