MySQL's JSON Data Type

tháng 2 11, 2016

MySQL 5.7 features a native JSON datatype. So just like a INT, CHAR, or other data type, you can now store valid JSON documents in a MySQL database. Previously you could put JSON formatted data into a CHAR, a TEXT, or similar data type. But it was a real pain to search this JSON data for items contained in them. But now you can store the JSON and there is a list functions to allow full access to this data in the column.


shell$ mysql -u root -p test
Enter password: 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.7.9 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE jdata (mydata JSON);
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO jdata (mydata) VALUES (JSON_OBJECT('name', 'Dave', 'height', 65));
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM jdata;
+-------------------------------+
| mydata                        |
+-------------------------------+
| {"name": "Dave", "height": 65} |
+-------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_EXTRACT(mydata,'$.name') FROM jdata;
+-------------------------------+
| JSON_EXTRACT(mydata,'$.name') |
+-------------------------------+
| "Dave"                        |
+-------------------------------+
1 row in set (0.00 sec)

mysql>

Please note the above SELECT statement. SQL is designed to pull data from columns but JSON data is in documents so we now need functions to pull keys from documents in that column. JSON_EXTRACT is looking in the mydata column and we use the $.name to say 'we want the value of this key in the current document'. Over the next few blog posts I will cover these functions and more.