MySQL对json的支持

Mysql自5.7以后，就支持json格式的数据。新增了很多的json相关的函数，json类型数据默认不允许为空。

新增json数据

首先，我们创建一个表，建表语句如下：

CREATE TABLE json_tab(
  id INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  json_data json
)

插入数据有两种方式，一种是直接插入json数据格式的字符串，另一种是通过json_array、json_object构建json数据。

mysql> INSERT INTO json_tab(json_data)
    -> values('{"id":1, "name":"gwx", "age":30, "idol":[{"name":"周杰伦", "age":40}, {"name":"谢霆锋", "age":40}]}');

我们现在来看看json_object及json_array的用法：

mysql> SELECT JSON_OBJECT('id',1,'name','gwx');
+----------------------------------+
| JSON_OBJECT('id',1,'name','gwx') |
+----------------------------------+
| {"id": 1, "name": "gwx"}         |
+----------------------------------+
1 row in set (0.01 sec)

mysql> SELECT JSON_ARRAY('id',1,'name','gwx');
+---------------------------------+
| JSON_ARRAY('id',1,'name','gwx') |
+---------------------------------+
| ["id", 1, "name", "gwx"]        |
+---------------------------------+
1 row in set (0.00 sec)

现在我们用这两种格式来组建好数据插入。

mysql> INSERT INTO json_tab(json_data)
    -> values (
    ->   json_object('id',2,'name','吴昊','age',30, 'idol',
    ->     json_array(
    ->       json_object('name','james','age',34),
    ->      json_object('name','吴亦凡', 'age',30)
    ->      )
    ->   )
    -> );
Query OK, 1 row affected (0.00 sec)

json数据查询

数据类型

获取json数据类型，object或array

mysql> select json_type(json_data) from json_tab;
+----------------------+
| json_type(json_data) |
+----------------------+
| OBJECT               |
| OBJECT               |
+----------------------+
2 rows in set (0.00 sec)

获取字段值

获取字段的值json_data->’$.字段名’或JSON_EXTRACT(column, path)

mysql> select json_data->'$.name',json_data->'$.age' from json_tab;
+---------------------+--------------------+
| json_data->'$.name' | json_data->'$.age' |
+---------------------+--------------------+
| "gwx"               | 30                 |
| "吴昊"              | 30                 |
+---------------------+--------------------+
2 rows in set (0.00 sec)

mysql> select json_extract(json_data, '$.name') from json_tab;
+-----------------------------------+
| json_extract(json_data, '$.name') |
+-----------------------------------+
| "gwx"                             |
| "吴昊"                            |
+-----------------------------------+
2 rows in set (0.00 sec)

将json转成json字符串类型

也有两种方式json_data->>’$.name’或json_unquote();

mysql> select json_data->>'$.name' from json_tab;
+----------------------+
| json_data->>'$.name' |
+----------------------+
| gwx                  |
| 吴昊                 |
+----------------------+
2 rows in set (0.01 sec)

mysql> select json_unquote(json_data) from json_tab;
+-------------------------------------------------------------------------------
------------------------------+
| json_unquote(json_data)
                              |
+-------------------------------------------------------------------------------
------------------------------+
| {"id": 1, "age": 30, "idol": [{"age": 40, "name": "周杰伦"}, {"age": 40, "name
": "谢霆锋"}], "name": "gwx"} |
| {"id": 2, "age": 30, "idol": [{"age": 34, "name": "james"}, {"age": 30, "name"
: "吴亦凡"}], "name": "吴昊"} |
+-------------------------------------------------------------------------------
------------------------------+
2 rows in set (0.00 sec)

查询json数组

json_data->’$.idol[*].name’;

mysql> select json_data->'$.idol[*]' from json_tab;
+----------------------------------------------------------------+
| json_data->'$.idol[*]'                                         |
+----------------------------------------------------------------+
| [{"age": 40, "name": "周杰伦"}, {"age": 40, "name": "谢霆锋"}] |
| [{"age": 34, "name": "james"}, {"age": 30, "name": "吴亦凡"}]  |
+----------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> select json_data->'$.idol[*].name' from json_tab;
+-----------------------------+
| json_data->'$.idol[*].name' |
+-----------------------------+
| ["周杰伦", "谢霆锋"]        |
| ["james", "吴亦凡"]         |
+-----------------------------+
2 rows in set (0.00 sec)

查询字段是否存在

JSON_CONTAINS()

# 查询是否有gwx这个人
mysql> select json_contains(json_data, '"gwx"', '$.name') from json_tab;
+---------------------------------------------+
| json_contains(json_data, '"gwx"', '$.name') |
+---------------------------------------------+
|                                           1 |
|                                           0 |
+---------------------------------------------+
2 rows in set (0.00 sec)