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文档以二进制格式存储，它提供一下功能：
- 对文档元素的快速读取访问
- 当服务器再次读取JSON文档时，不需要重新解析文本获取该值
- 通过键或数组引擎查找子对象或嵌套值，而不要读取文档中的所有值
示例
create table emp_details(
emp_no int primary key,
details json
);
插入
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"}}');
查询
可以使用->和->>运算符检索JSON列的字段：
select emp_no, details->'$.address.pin' pin
from emp_details;
如果不用引号检索数据，可以使用->>运算符：
select emp_no, details->>'$.address.pin' pin
from emp_details;
也可以使用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;
数组的路径是通过下标来表示的。第一个元素的下标是 0。
select json_extract('[10, 20, [30, 40]]', '$[0]');
除此之外，还可通过 [M to N] 获取数组的子集：
select json_extract('[10, 20, [30, 40]]', '$[0 to 1]');
也可通过 [*] 获取数组中的所有元素:
select json_extract('[10, 20, [30, 40]]', '$[*]');
JSON函数
优雅浏览：
想要以优雅的格式显示JSON值，请使用JSON_PRETTY()函数：
select emp_no, JSON_PRETTY(details)
from emp_details
JSON_KEYS：
返回 JSON 文档最外层的 key，如果指定了 path，则返回该 path 对应元素最外层的 key：
select JSON_KEYS(details)
from emp_details
where emp_no = 1;
JSON_LENGTH：
给出JSON文档中的元素数：
select JSON_LENGTH(details)
from emp_details
where emp_no = 1;
JSON_ARRAY：
创建JSON数组
select JSON_ARRAY(1, "abc", NULL, TRUE, now());
JSON_OBJECT：
创建JSON对象
select JSON_OBJECT('emp_no', 2, 'name', 'tom');
JSON_QUOTE：
通过使用双引号将字符串包装并转义内部引号和其他字符，将字符串作为JSON值引用，然后将结果作为utf8mb4字符串返回。如果参数为NULL则返回NULL。
select json_quote('null'), json_quote('"null"'), json_quote('[1, 2, 3]');
查找值：
可以在where字句中使用col->>path运算符来引用JSON的某一列
select emp_no from emp_details
where details->>'$.address.pin'="560103";
也可以使用JSON_CONTAINS函数查询数据。如果找到了数据，则返回1，否则返回0：
select JSON_CONTAINS(details->>'$.address.pin', "560103")
from emp_details;
查找键：
查询一个或多个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;
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')
SELECT JSON_SEARCH(@j, 'all', 'abc');
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
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 |
+-------+
修改
可以使用三种不同的函数来修改数据：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;
==JSON_INSERT()：==插入值，但不替换现有的值
update emp_details
set details = JSON_INSERT(details, "$.address.pin", "560132", "$.address.line4", "A Wing")
where emp_no = 1;
-- 这种情况pin不会被更新，只会添加一个新的字段address.line4
== JSON_REPLACE()：==替换现有的值
update emp_details
set details = JSON_REPLACE(details, "$.address.pin", "560132", "$.address.line5", "Landmark")
where emp_no = 1;
-- 这种情况下，line5不会被添加，只有pin会被更新
JSON_ARRAY_APPEND：
-- 语法
JSON_ARRAY_APPEND(json_doc, path, val[, path, val] ...)
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)
JSON_ARRAY_INSERT：
-- 语法
JSON_ARRAY_INSERT(json_doc, path, val[, path, val] ...)
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)
JSON_MERGE：
-- 语法
JSON_MERGE(json_doc, json_doc[, json_doc] ...)
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)
JSON_MERGE_PATCH：
-- 语法
JSON_MERGE_PATCH(json_doc, json_doc[, json_doc] ...)
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>
JSON_MERGE_PRESERVE：
-- 语法
JSON_MERGE_PRESERVE(json_doc, json_doc[, json_doc] ...)
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>
删除
JSON_REMOVE能从JSON文档中删除数据：
update emp_details
set details = JSON_REMOVE(details, "$.address.linel")
where emp_no = 1;
返回JSON值属性
== JSON_DEPTH：==
-- 语法
JSON_DEPTH(json_doc)
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>
JSON_TYPE：
-- 语法
JSON_TYPE(json_val)
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>
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
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)
904
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
