文章目录
备注:
版本: MySQL 8.0
一. Json数据存储
MySQL 8.0提供了json数据类型来存储json数据。
create table test_json(id int,json_data json);
二. Json数据insert
代码:
INSERT INTO test_json VALUES(1,'{"key1": "value1", "key2": "value2"}');
三. json数据update
代码:
update test_json set json_data = '{"key1": "1", "key2": "2"}';
测试记录:
四.json数据查询
测试记录:
mysql> select id,json_data,json_data->'$.key2' as rst from test_json;
+------+----------------------------+------+
| id | json_data | rst |
+------+----------------------------+------+
| 1 | {"key1": "1", "key2": "2"} | "2" |
+------+----------------------------+------+
1 row in set (0.00 sec)
五. 常用的JSON函数
5.1 创建json值的函数
5.1.1 json_array
语法:
JSON_ARRAY([*`val`*[, *`val`*] ...])
测试记录:
mysql> SELECT JSON_ARRAY(1, "abc", NULL, TRUE, CURTIME());
+---------------------------------------------+
| JSON_ARRAY(1, "abc", NULL, TRUE, CURTIME()) |
+---------------------------------------------+
| [1, "abc", null, true, "12:35:57.000000"] |
+---------------------------------------------+
1 row in set (0.00 sec)
5.1.2 json_object
语法:
JSON_OBJECT([*`key`*, *`val`*[, *`key`*, *`val`*] ...])
测试记录:
mysql> select json_object('deptno' ,deptno,'dname' , dname) from dept;
+-----------------------------------------------+
| json_object('deptno' ,deptno,'dname' , dname) |
+-----------------------------------------------+
| {"dname": "ACCOUNTING", "deptno": 10} |
| {"dname": "RESEARCH", "deptno": 20} |
| {"dname": "SALES", "deptno": 30} |
| {"dname": "OPERATIONS", "deptno": 40} |
+-----------------------------------------------+
4 rows in set (0.00 sec)
5.1.3 JSON_QUOTE
通过使用双引号将字符串包装并转义内部引号和其他字符,将字符串作为JSON值引用,然后将结果作为utf8mb4字符串返回。如果参数为NULL则返回NULL。
使用CAST(value AS JSON) 强制转化也可以
语法:
JSON_QUOTE(*`string`*)
5.2 检索json数据的值
5.2.1 JSON_CONTAINS
通过返回1或0,指示给定的候选JSON文档是否包含在目标JSON文档中,或者如果提供了路径参数,则指示是否在目标中的特定路径中找到候选文档。如果任何参数为NULL,或者如果路径参数不标识目标文档的某个部分,则返回NULL。如果target或candidate不是有效的JSON文档,或者path参数不是有效的路径表达式或包含*或**通配符,则会发生错误。
语法:
JSON_CONTAINS(target, candidate[, path])
测试记录:
mysql> SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}';
Query OK, 0 rows affected (0.00 sec)
mysql> SET @j2 = '1';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT JSON_CONTAINS(@j, @j2, '$.a');
+-------------------------------+
| JSON_CONTAINS(@j, @j2, '$.a') |
+-------------------------------+
| 1 |
+-------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_CONTAINS(@j, @j2, '$.b');
+-------------------------------+
| JSON_CONTAINS(@j, @j2, '$.b') |
+-------------------------------+
| 0 |
+-------------------------------+
1 row in set (0.00 sec)
5.2.2 JSON_CONTAINS_PATH
返回0或1以指示JSON文档是否包含给定路径上的数据。如果任何参数为NULL则返回NULL。如果json_doc参数不是一个有效的JSON文档,任何路径参数不是一个有效的路径表达式,或者one_or_all不是’one’或’all’,则会发生错误。
语法:
JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...)
测试记录:
mysql> SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT JSON_CONTAINS_PATH(@j, 'one', '$.a', '$.e');
+---------------------------------------------+
| JSON_CONTAINS_PATH(@j, 'one', '$.a', '$.e') |
+---------------------------------------------+
| 1 |
+---------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_CONTAINS_PATH(@j, 'all', '$.a', '$.e');
+---------------------------------------------+
| JSON_CONTAINS_PATH(@j, 'all', '$.a', '$.e') |
+---------------------------------------------+
| 0 |
+---------------------------------------------+
1 row in set (0.00 sec)
5.2.3 JSON_EXTRACT
从JSON文档中返回数据,从路径参数匹配的文档部分中选择数据。如果任何参数为NULL或文档中没有路径定位值,则返回NULL。如果json_doc参数不是有效的JSON文档或任何路径参数不是有效的路径表达式,就会发生错误。
代码:
JSON_EXTRACT(json_doc, path[, path] ...)
测试记录:
mysql> SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]');
+--------------------------------------------+
| JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]') |
+--------------------------------------------+
| 20 |
+--------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]', '$[0]');
+----------------------------------------------------+
| JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]', '$[0]') |
+----------------------------------------------------+
| [20, 10] |
+----------------------------------------------------+
1 row in set (0.00 sec)
mysql>
5.2.4 column->path
当与两个参数一起使用时,->操作符充当JSON_EXTRACT()函数的别名,左边是列标识符,右边是根据JSON文档(列值)求值的JSON路径(字符串文字)。您可以在SQL语句中任何出现列引用的地方使用这种表达式。
代码:
select json_data, json_extract(json_data, "$.key1") from test_json;
select json_data, json_data ->"$.key1" from test_json;
测试记录:
5.2.5 JSON_UNQUOTE
语法:
JSON_UNQUOTE(column -> path)
代码:
select json_data, json_data ->"$.key1" from test_json;
select json_data, json_unquote(json_data ->"$.key1") from test_json;
测试记录:
5.2.6 JSON_KEYS
作为JSON数组返回JSON对象的顶层值的键,或者,如果给出了路径参数,则返回所选路径的顶层键。如果任何参数为NULL,则返回NULL, json_doc参数不是对象,或者如果给出了路径,则不定位对象。如果json_doc参数不是有效的JSON文档,或者path参数不是有效的路径表达式,或者包含*或**通配符,则会发生错误。
语法:
JSON_KEYS(json_doc[, path])
测试记录:
mysql> SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}');
+---------------------------------------+
| JSON_KEYS('{"a": 1, "b": {"c": 30}}') |
+---------------------------------------+
| ["a", "b"] |
+---------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}', '$.b');
+----------------------------------------------+
| JSON_KEYS('{"a": 1, "b": {"c": 30}}', '$.b') |
+----------------------------------------------+
| ["c"] |
+----------------------------------------------+
1 row in set (0.00 sec)
5.2.7 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] ...])
测试记录:
mysql> SET @j = '["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT JSON_SEARCH(@j, 'one', 'abc');
+-------------------------------+
| JSON_SEARCH(@j, 'one', 'abc') |
+-------------------------------+
| "$[0]" |
+-------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_SEARCH(@j, 'all', 'abc');
+-------------------------------+
| JSON_SEARCH(@j, 'all', 'abc') |
+-------------------------------+
| ["$[0]", "$[2].x"] |
+-------------------------------+
1 row in set (0.00 sec)
mysql>
5.2.8 JSON_VALUE
JSON_VALUE() was introduced in MySQL 8.0.21.
从指定文档中给定的路径处的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 |
+-------+
5.3 修改json数据的值
5.3.1 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)
5.3.2 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)
5.3.3 JSON_INSERT
代码:
JSON_INSERT(json_doc, path, val[, path, val] ...)
测试记录:
mysql> SET @j = '{ "a": 1, "b": [2, 3]}';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT JSON_INSERT(@j, '$.a', 10, '$.c', '[true, false]');
+----------------------------------------------------+
| JSON_INSERT(@j, '$.a', 10, '$.c', '[true, false]') |
+----------------------------------------------------+
| {"a": 1, "b": [2, 3], "c": "[true, false]"} |
+----------------------------------------------------+
1 row in set (0.00 sec)
mysql>
5.3.4 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)
5.3.5 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>
5.3.6 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>
5.3.7 JSON_REMOVE
代码:
JSON_REMOVE(json_doc, path[, path] ...)
测试记录:
mysql> SET @j = '["a", ["b", "c"], "d"]';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT JSON_REMOVE(@j, '$[1]');
+-------------------------+
| JSON_REMOVE(@j, '$[1]') |
+-------------------------+
| ["a", "d"] |
+-------------------------+
1 row in set (0.00 sec)
mysql>
5.3.8 JSON_REPLACE
代码:
JSON_REPLACE(json_doc, path, val[, path, val] ...)
测试记录:
mysql> SET @j = '{ "a": 1, "b": [2, 3]}';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT JSON_REPLACE(@j, '$.a', 10, '$.c', '[true, false]');
+-----------------------------------------------------+
| JSON_REPLACE(@j, '$.a', 10, '$.c', '[true, false]') |
+-----------------------------------------------------+
| {"a": 10, "b": [2, 3]} |
+-----------------------------------------------------+
1 row in set (0.00 sec)
mysql>
5.3.9 JSON_SET
语法:
JSON_SET(json_doc, path, val[, path, val] ...)
测试记录:
mysql> SET @j = '{ "a": 1, "b": [2, 3]}';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT JSON_SET(@j, '$.a', 10, '$.c', '[true, false]');
+-------------------------------------------------+
| JSON_SET(@j, '$.a', 10, '$.c', '[true, false]') |
+-------------------------------------------------+
| {"a": 10, "b": [2, 3], "c": "[true, false]"} |
+-------------------------------------------------+
1 row in set (0.00 sec)
mysql>
5.3.10
语法:
JSON_UNQUOTE(json_val)
测试记录:
mysql> SET @j = '"abc"';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @j, JSON_UNQUOTE(@j);
+-------+------------------+
| @j | JSON_UNQUOTE(@j) |
+-------+------------------+
| "abc" | abc |
+-------+------------------+
1 row in set (0.00 sec)
mysql>
5.4 返回JSON值属性的函数
5.4.1 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>
5.4.2 JSON_LENGTH
语法:
JSON_LENGTH(json_doc[, path])
测试记录:
mysql> SELECT JSON_LENGTH('[1, 2, {"a": 3}]');
+---------------------------------+
| JSON_LENGTH('[1, 2, {"a": 3}]') |
+---------------------------------+
| 3 |
+---------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_LENGTH('{"a": 1, "b": {"c": 30}}');
+-----------------------------------------+
| JSON_LENGTH('{"a": 1, "b": {"c": 30}}') |
+-----------------------------------------+
| 2 |
+-----------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_LENGTH('{"a": 1, "b": {"c": 30}}', '$.b');
+------------------------------------------------+
| JSON_LENGTH('{"a": 1, "b": {"c": 30}}', '$.b') |
+------------------------------------------------+
| 1 |
+------------------------------------------------+
1 row in set (0.00 sec)
mysql>
5.4.3 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>
5.5 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)
5.6 json实用函数
5.6.1 JSON_PRETTY
语法:
JSON_PRETTY(json_val)
测试记录:
mysql> select * from test_json;
+------+----------------------------+
| id | json_data |
+------+----------------------------+
| 1 | {"key1": "1", "key2": "2"} |
+------+----------------------------+
1 row in set (0.00 sec)
mysql>
mysql>
mysql> select json_pretty(json_data) as new_json_data from test_json;
+----------------------------------+
| new_json_data |
+----------------------------------+
| {
"key1": "1",
"key2": "2"
} |
+----------------------------------+
1 row in set (0.00 sec)
mysql>
参考:
- https://dev.mysql.com/doc/refman/8.0/en/json.html