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 ...
