O'Reilly logo

MySQL 8 Cookbook by Karthik Appigatla

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required