MySQL——JSON类型_mysql json

JSON类型

从MySQL5.7.8开始，MySQL就支持了JSON数据类型。之前，这类数据不是单独的数据类型，会被存储为字符串。新的JSON数据类型提供了自动验证的JSON文档以及优化的存储格式。

对于 JSON 文档，KEY 名不能重复。如果插入的值中存在重复 KEY，在 MySQL 8.0.3 之前，遵循 first duplicate key wins 原则，会保留第一个 KEY，后面的将被丢弃掉。

从 MySQL 8.0.3 开始，遵循的是 last duplicate key wins 原则，只会保留最后一个 KEY。

JSON文档以二进制格式存储，它提供一下功能：

1. 对文档元素的快速读取访问
2. 当服务器再次读取JSON文档时，不需要重新解析文本获取该值
3. 通过键或数组引擎查找子对象或嵌套值，而不要读取文档中的所有值

示例

create table emp_details(
	emp_no int primary key,
	details json 
);
1
2
3
4

插入

insert into emp_details(emp_no, details)
values (1, 
	'{"location":"IN", "phone":"+11800000000", "email":"abc@example.com","address":{"linel":"abc", "line2":"xyz street","city":"Bangalore","pin":"560103"}}');
1
2
3

查询

可以使用->和->>运算符检索JSON列的字段：

select emp_no, details->'$.address.pin' pin 
from emp_details;
1
2

如果不用引号检索数据，可以使用->>运算符：

select emp_no, details->>'$.address.pin' pin 
from emp_details;
1
2

也可以使用JSON_EXTRACT函数
JSON_EXTRACT(json_doc, path[, path] ...)
其中，json_doc 是 JSON 文档，path 是路径。该函数会从 JSON 文档提取指定路径（path）的元素。如果指定 path 不存在，会返回 NULL。可指定多个 path，匹配到的多个值会以数组形式返回。

select JSON_EXTRACT(details, "$.address.pin", "$.phone")
from emp_details;
1
2

数组的路径是通过下标来表示的。第一个元素的下标是 0。

select json_extract('[10, 20, [30, 40]]', '$[0]');
1

除此之外，还可通过 [M to N] 获取数组的子集：

select json_extract('[10, 20, [30, 40]]', '$[0 to 1]');
1

也可通过 [*] 获取数组中的所有元素:

select json_extract('[10, 20, [30, 40]]', '$[*]');
1

JSON函数

优雅浏览：
想要以优雅的格式显示JSON值，请使用JSON_PRETTY()函数：

select emp_no, JSON_PRETTY(details)
from emp_details
1
2

JSON_KEYS：
返回 JSON 文档最外层的 key，如果指定了 path，则返回该 path 对应元素最外层的 key：

select JSON_KEYS(details)
from emp_details
where emp_no = 1;
1
2
3
4

JSON_LENGTH：
给出JSON文档中的元素数：

select JSON_LENGTH(details)
from emp_details
where emp_no = 1;
1
2
3

JSON_ARRAY：
创建JSON数组

select JSON_ARRAY(1, "abc", NULL, TRUE, now());
1

JSON_OBJECT：
创建JSON对象

select JSON_OBJECT('emp_no', 2, 'name', 'tom');
1

JSON_QUOTE：
通过使用双引号将字符串包装并转义内部引号和其他字符，将字符串作为JSON值引用，然后将结果作为utf8mb4字符串返回。如果参数为NULL则返回NULL。

select json_quote('null'), json_quote('"null"'), json_quote('[1, 2, 3]');

1
2

查找值：
可以在where字句中使用col->>path运算符来引用JSON的某一列

select emp_no from emp_details
where details->>'$.address.pin'="560103";
1
2

也可以使用JSON_CONTAINS函数查询数据。如果找到了数据，则返回1，否则返回0：

select JSON_CONTAINS(details->>'$.address.pin', "560103")
from emp_details;
1
2

查找键：

查询一个或多个key是否存在，可以使用JSON_CONTAINS_PATH函数

-- 至少一个key存在使用参数 one ，存在返回1，不存在则返回0：
select JSON_CONTAINS_PATH(details, 'one', "$.address.linel")
from emp_details;

select JSON_CONTAINS_PATH(details, 'one', "$.address.linel", "$.address.line5")
from emp_details;


-- 多个key同时存在使用参数 all，同时存在返回1，有一个不存在则返回0
select JSON_CONTAINS_PATH(details, 'all', "$.address.linel", "$.address.line5")
from emp_details;

1
2
3
4
5
6
7
8
9
10
11
12

JSON_SEARCH：
返回JSON文档中给定字符串的路径。如果任何json_doc、search_str或path参数为NULL，则返回NULL;文档中不存在路径;或者没有找到search_str。如果json_doc参数不是一个有效的JSON文档，任何路径参数不是一个有效的路径表达式，one_or_all不是’one’或’all’，或者escape_char不是一个常量表达式，就会发生错误。

-- 语法
JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])


SET @j = '["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]';
SELECT JSON_SEARCH(@j, 'one', 'abc')
1
2
3
4
5
6

SELECT JSON_SEARCH(@j, 'all', 'abc');
1

JSON_VALUE：
从指定文档中给定的路径处的JSON文档中提取值，并返回提取的值，可以将其转换为所需的类型。完整的语法如下所示:

-- 语法
JSON_VALUE(json_doc, path [RETURNING type] [on_empty] [on_error])

on_empty:
    {NULL | ERROR | DEFAULT value} ON EMPTY

on_error:
    {NULL | ERROR | DEFAULT value} ON ERROR
1
2
3
4
5
6
7
8

mysql> SELECT JSON_VALUE('{"fname": "Joe", "lname": "Palmer"}', '$.fname');
+--------------------------------------------------------------+
| JSON_VALUE('{"fname": "Joe", "lname": "Palmer"}', '$.fname') |
+--------------------------------------------------------------+
| Joe                                                          |
+--------------------------------------------------------------+

mysql> SELECT JSON_VALUE('{"item": "shoes", "price": "49.95"}', '$.price'
    -> RETURNING DECIMAL(4,2)) AS price;
+-------+
| price |
+-------+
| 49.95 |
+-------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14

修改

可以使用三种不同的函数来修改数据：JSON_SET()、JSON_INSERT()和JSON_REPLACE()。在MySQL8之前的版本中，还需要对整个列进行完整的更新，但这并不是最佳的方法。

JSON_SET()： 替换现有值并添加不存在的值

-- 假设替换员工的pin码，并添加昵称的详细信息
update emp_details
set details = JSON_SET(details, "$.address.pin", "560100", "$.nickname", "kai")
where emp_no = 1;
1
2
3
4

==JSON_INSERT()：==插入值，但不替换现有的值

update emp_details 
set details = JSON_INSERT(details, "$.address.pin", "560132", "$.address.line4", "A Wing")
where emp_no = 1;
-- 这种情况pin不会被更新，只会添加一个新的字段address.line4
1
2
3
4

== JSON_REPLACE()：==替换现有的值

update emp_details 
set details = JSON_REPLACE(details, "$.address.pin", "560132", "$.address.line5", "Landmark")
where emp_no = 1;
-- 这种情况下，line5不会被添加，只有pin会被更新
1
2
3
4

JSON_ARRAY_APPEND：

-- 语法
JSON_ARRAY_APPEND(json_doc, path, val[, path, val] ...)
1
2

mysql> SET @j = '["a", ["b", "c"], "d"]';
Query OK, 0 rows affected (0.00 sec)

mysql>  SELECT JSON_ARRAY_APPEND(@j, '$[1]', 1);
+----------------------------------+
| JSON_ARRAY_APPEND(@j, '$[1]', 1) |
+----------------------------------+
| ["a", ["b", "c", 1], "d"]        |
+----------------------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10

JSON_ARRAY_INSERT：

-- 语法
JSON_ARRAY_INSERT(json_doc, path, val[, path, val] ...)
1
2

mysql> SET @j = '["a", {"b": [1, 2]}, [3, 4]]';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT JSON_ARRAY_INSERT(@j, '$[1]', 'x');
+------------------------------------+
| JSON_ARRAY_INSERT(@j, '$[1]', 'x') |
+------------------------------------+
| ["a", "x", {"b": [1, 2]}, [3, 4]]  |
+------------------------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10

JSON_MERGE：

-- 语法
JSON_MERGE(json_doc, json_doc[, json_doc] ...)
1
2

mysql> SELECT JSON_MERGE('[1, 2]', '[true, false]');
+---------------------------------------+
| JSON_MERGE('[1, 2]', '[true, false]') |
+---------------------------------------+
| [1, 2, true, false]                   |
+---------------------------------------+
1 row in set, 1 warning (0.00 sec)
1
2
3
4
5
6
7

JSON_MERGE_PATCH：

-- 语法
JSON_MERGE_PATCH(json_doc, json_doc[, json_doc] ...)
1
2

mysql> SELECT JSON_MERGE_PATCH('[1, 2]', '[true, false]');
+---------------------------------------------+
| JSON_MERGE_PATCH('[1, 2]', '[true, false]') |
+---------------------------------------------+
| [true, false]                               |
+---------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_MERGE_PATCH('{"name": "x"}', '{"id": 47}');
+-------------------------------------------------+
| JSON_MERGE_PATCH('{"name": "x"}', '{"id": 47}') |
+-------------------------------------------------+
| {"id": 47, "name": "x"}                         |
+-------------------------------------------------+
1 row in set (0.00 sec)

mysql>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

JSON_MERGE_PRESERVE：

-- 语法
JSON_MERGE_PRESERVE(json_doc, json_doc[, json_doc] ...)
1
2

mysql> SELECT JSON_MERGE_PRESERVE('[1, 2]', '[true, false]');
+------------------------------------------------+
| JSON_MERGE_PRESERVE('[1, 2]', '[true, false]') |
+------------------------------------------------+
| [1, 2, true, false]                            |
+------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_MERGE_PRESERVE('{"name": "x"}', '{"id": 47}');
+----------------------------------------------------+
| JSON_MERGE_PRESERVE('{"name": "x"}', '{"id": 47}') |
+----------------------------------------------------+
| {"id": 47, "name": "x"}                            |
+----------------------------------------------------+
1 row in set (0.00 sec)

mysql>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

删除

JSON_REMOVE能从JSON文档中删除数据：

update emp_details
set details = JSON_REMOVE(details, "$.address.linel")
where emp_no = 1;
1
2
3

返回JSON值属性

== JSON_DEPTH：==

-- 语法
JSON_DEPTH(json_doc)
1
2

mysql> SELECT JSON_DEPTH('{}'), JSON_DEPTH('[]'), JSON_DEPTH('true');
+------------------+------------------+--------------------+
| JSON_DEPTH('{}') | JSON_DEPTH('[]') | JSON_DEPTH('true') |
+------------------+------------------+--------------------+
|                1 |                1 |                  1 |
+------------------+------------------+--------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_DEPTH('[10, 20]'), JSON_DEPTH('[[], {}]');
+------------------------+------------------------+
| JSON_DEPTH('[10, 20]') | JSON_DEPTH('[[], {}]') |
+------------------------+------------------------+
|                      2 |                      2 |
+------------------------+------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_DEPTH('[10, {"a": 20}]');
+-------------------------------+
| JSON_DEPTH('[10, {"a": 20}]') |
+-------------------------------+
|                             3 |
+-------------------------------+
1 row in set (0.00 sec)

mysql>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

JSON_TYPE：

-- 语法
JSON_TYPE(json_val)
1
2

mysql> SET @j = '{"a": [10, true]}';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT JSON_TYPE(@j);
+---------------+
| JSON_TYPE(@j) |
+---------------+
| OBJECT        |
+---------------+
1 row in set (0.00 sec)

mysql>  SELECT JSON_TYPE(JSON_EXTRACT(@j, '$.a'));
+------------------------------------+
| JSON_TYPE(JSON_EXTRACT(@j, '$.a')) |
+------------------------------------+
| ARRAY                              |
+------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_TYPE(JSON_EXTRACT(@j, '$.a[0]'));
+---------------------------------------+
| JSON_TYPE(JSON_EXTRACT(@j, '$.a[0]')) |
+---------------------------------------+
| INTEGER                               |
+---------------------------------------+
1 row in set (0.00 sec)

mysql>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

JSON_TABLE：

-- 语法
JSON_TABLE(
    expr,
    path COLUMNS (column_list)
)   [AS] alias

column_list:
    column[, column][, ...]

column:
    name FOR ORDINALITY
    |  name type PATH string path [on_empty] [on_error]
    |  name type EXISTS PATH string path
    |  NESTED [PATH] path COLUMNS (column_list)

on_empty:
    {NULL | DEFAULT json_string | ERROR} ON EMPTY

on_error:
    {NULL | DEFAULT json_string | ERROR} ON ERROR
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

mysql> SELECT *
    -> FROM
    ->   JSON_TABLE(
    ->     '[{"a":"3"},{"a":2},{"b":1},{"a":0},{"a":[1,2]}]',
    ->     "$[*]"
    ->     COLUMNS(
    ->       rowid FOR ORDINALITY,
    ->       ac VARCHAR(100) PATH "$.a" DEFAULT '111' ON EMPTY DEFAULT '999' ON ERROR,
    ->       aj JSON PATH "$.a" DEFAULT '{"x": 333}' ON EMPTY,
    ->       bx INT EXISTS PATH "$.b"
    ->     )
    ->   ) AS tt;

+-------+------+------------+------+
| rowid | ac   | aj         | bx   |
+-------+------+------------+------+
|     1 | 3    | "3"        |    0 |
|     2 | 2    | 2          |    0 |
|     3 | 111  | {"x": 333} |    1 |
|     4 | 0    | 0          |    0 |
|     5 | 999  | [1, 2]     |    0 |
+-------+------+------------+------+
5 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23