How to update JSON data type column in MySQL 5.7.10?

by

Thanks @wchiquito for pointing me right direction. I solved the problem. Here is how I did it.

mysql> select * from t1;
+----------------------------------------+------+
| data                                   | id   |
+----------------------------------------+------+
| {"key1": "value1", "key2": "VALUE2"}   |    1 |
| {"key2": "VALUE2"}                     |    2 |
| {"key2": "VALUE2"}                     |    1 |
| {"a": "x", "b": "y", "key2": "VALUE2"} |    1 |
+----------------------------------------+------+
4 rows in set (0.00 sec)

mysql> update t1 set data = JSON_SET(data, "$.key2", "I am ID2") where id = 2;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t1;
+----------------------------------------+------+
| data                                   | id   |
+----------------------------------------+------+
| {"key1": "value1", "key2": "VALUE2"}   |    1 |
| {"key2": "I am ID2"}                   |    2 |
| {"key2": "VALUE2"}                     |    1 |
| {"a": "x", "b": "y", "key2": "VALUE2"} |    1 |
+----------------------------------------+------+
4 rows in set (0.00 sec)

mysql> update t1 set data = JSON_SET(data, "$.key3", "I am ID3") where id = 2;
Query OK, 1 row affected (0.07 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t1;
+------------------------------------------+------+
| data                                     | id   |
+------------------------------------------+------+
| {"key1": "value1", "key2": "VALUE2"}     |    1 |
| {"key2": "I am ID2", "key3": "I am ID3"} |    2 |
| {"key2": "VALUE2"}                       |    1 |
| {"a": "x", "b": "y", "key2": "VALUE2"}   |    1 |
+------------------------------------------+------+
4 rows in set (0.00 sec)

EDIT:
If you want to add an array, use JSON_ARRAY like 

update t1 set data = JSON_SET(data, "$.key4", JSON_ARRAY('Hello','World!')) where id = 2;

enter image description here

More Related Contents:

  1. How to search JSON data in MySQL?
  2. MySQL JSON: How to find object from key value
  3. Using MySQL JSON field to join on a table
  4. MySQL search json value by key in array
  5. How to convert result table to JSON array in MySQL
  6. Storing Data in MySQL as JSON
  7. How do I generate nested json objects using mysql native json functions?
  8. How can write queries in MySQL that can parse JSON data in a column?
  9. Convert JSON array in MySQL to rows
  10. Native JSON support in MYSQL 5.7 : what are the pros and cons of JSON data type in MYSQL?
  11. How to retrieve JSON data from MySQL?
  12. How to create and insert a JSON object using MySQL queries?
  13. MySQL vs. JSON – Why? [closed]
  14. How to search JSON array in MySQL?
  15. Delimiters in MySQL
  16. Group by clause in mySQL and postgreSQL, why the error in postgreSQL?
  17. ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
  18. MySQL SELECT WHERE datetime matches day (and not necessarily time)
  19. How to convert timestamp to datetime in MySQL?
  20. MySQL varchar index length
  21. Ruby on Rails 3 Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ on OSX
  22. Best practices for SQL varchar column length [closed]
  23. MySQL – Fetching lowest value
  24. What are differences between INSERT and UPDATE in MySQL?
  25. How to produce “phantom read” in REPEATABLE READ? (MySQL)
  26. How to delete duplicate rows from a MySQL table
  27. mysql: why comparing a ‘string’ to 0 gives true?
  28. mySQL Error 1040: Too Many Connection
  29. Unserialize through query at database level itself
  30. Detecting utf8 broken characters in MySQL

Leave a Comment