小丑西瓜9

这个屌丝很懒，什么也没留下！

热门标签

MySQL处理Json数据_mysql处理json字符串

作者：小丑西瓜9 | 2024-03-23 19:08:21

踩

mysql处理json字符串

备注：

版本: MySQL 8.0

一. Json数据存储

MySQL 8.0提供了json数据类型来存储json数据。

create table test_json(id int,json_data json);
1

二. Json数据insert

代码:

INSERT INTO test_json VALUES(1,'{"key1": "value1", "key2": "value2"}');
1

三. json数据update

代码:

update test_json set json_data = '{"key1": "1", "key2": "2"}';
1

测试记录:

四.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)
1
2
3
4
5
6
7

五. 常用的JSON函数

5.1 创建json值的函数

5.1.1 json_array

语法:

JSON_ARRAY([*`val`*[, *`val`*] ...])
1

测试记录:

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)
1
2
3
4
5
6
7

5.1.2 json_object

语法:

JSON_OBJECT([*`key`*, *`val`*[, *`key`*, *`val`*] ...])
1

测试记录:

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)
1
2
3
4
5
6
7
8
9
10

5.1.3 JSON_QUOTE

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

使用CAST(value AS JSON) 强制转化也可以

语法:

JSON_QUOTE(*`string`*)
1

5.2 检索json数据的值

5.2.1 JSON_CONTAINS

通过返回1或0，指示给定的候选JSON文档是否包含在目标JSON文档中，或者如果提供了路径参数，则指示是否在目标中的特定路径中找到候选文档。如果任何参数为NULL，或者如果路径参数不标识目标文档的某个部分，则返回NULL。如果target或candidate不是有效的JSON文档，或者path参数不是有效的路径表达式或包含*或**通配符，则会发生错误。

语法:

JSON_CONTAINS(target, candidate[, path])
1

测试记录:

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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

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] ...)
1

测试记录:

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)

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

5.2.3 JSON_EXTRACT

从JSON文档中返回数据，从路径参数匹配的文档部分中选择数据。如果任何参数为NULL或文档中没有路径定位值，则返回NULL。如果json_doc参数不是有效的JSON文档或任何路径参数不是有效的路径表达式，就会发生错误。
代码:

JSON_EXTRACT(json_doc, path[, path] ...)
1

测试记录:

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>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

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;
1
2

测试记录:

5.2.5 JSON_UNQUOTE

语法:

JSON_UNQUOTE(column -> path)
1

代码:

select json_data, json_data ->"$.key1" from test_json;
select json_data, json_unquote(json_data ->"$.key1") from test_json;
1
2

测试记录:

5.2.6 JSON_KEYS

作为JSON数组返回JSON对象的顶层值的键，或者，如果给出了路径参数，则返回所选路径的顶层键。如果任何参数为NULL，则返回NULL, json_doc参数不是对象，或者如果给出了路径，则不定位对象。如果json_doc参数不是有效的JSON文档，或者path参数不是有效的路径表达式，或者包含*或**通配符，则会发生错误。

语法:

JSON_KEYS(json_doc[, path])
1

测试记录:

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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

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] ...])
1

测试记录:

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>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

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
1
2
3
4
5
6
7

测试记录:

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

5.3 修改json数据的值

5.3.1 JSON_ARRAY_APPEND

语法:

JSON_ARRAY_APPEND(json_doc, path, val[, path, val] ...)
1

测试记录:

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

5.3.2 JSON_ARRAY_INSERT

代码:

JSON_ARRAY_INSERT(json_doc, path, val[, path, val] ...)
1

测试记录:

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

5.3.3 JSON_INSERT

代码:

JSON_INSERT(json_doc, path, val[, path, val] ...)
1

测试记录:

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>
1
2
3
4
5
6
7
8
9
10
11
12

5.3.4 JSON_MERGE

代码:

JSON_MERGE(json_doc, json_doc[, json_doc] ...)
1

测试记录:

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

5.3.5 JSON_MERGE_PATCH

代码:

JSON_MERGE_PATCH(json_doc, json_doc[, json_doc] ...)
1

测试记录:

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

5.3.6 JSON_MERGE_PRESERVE

代码:

JSON_MERGE_PRESERVE(json_doc, json_doc[, json_doc] ...)
1

测试记录:

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

5.3.7 JSON_REMOVE

代码:

JSON_REMOVE(json_doc, path[, path] ...)
1

测试记录:

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>
1
2
3
4
5
6
7
8
9
10
11
12

5.3.8 JSON_REPLACE

代码:

JSON_REPLACE(json_doc, path, val[, path, val] ...)
1

测试记录:

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>
1
2
3
4
5
6
7
8
9
10
11
12

5.3.9 JSON_SET

语法:

JSON_SET(json_doc, path, val[, path, val] ...)
1

测试记录:

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>
1
2
3
4
5
6
7
8
9
10
11
12

5.3.10

语法:

JSON_UNQUOTE(json_val)
1

测试记录:

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>
1
2
3
4
5
6
7
8
9
10
11
12

5.4 返回JSON值属性的函数

5.4.1 JSON_DEPTH

语法:

JSON_DEPTH(json_doc)
1

测试记录:

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

5.4.2 JSON_LENGTH

语法:

JSON_LENGTH(json_doc[, path])
1

测试记录:

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>
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

5.4.3 JSON_TYPE

语法:

JSON_TYPE(json_val)
1

测试记录:

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

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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

测试记录:

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

5.6 json实用函数

5.6.1 JSON_PRETTY

语法:

JSON_PRETTY(json_val)
1

测试记录:

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>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

参考:

https://dev.mysql.com/doc/refman/8.0/en/json.html

声明：本文内容由网友自发贡献，转载请注明出处：【wpsshop博客】

MySQL处理Json数据_mysql处理json字符串

文章目录

备注：

一. Json数据存储

二. Json数据insert

三. json数据update

四.json数据查询

五. 常用的JSON函数

5.1 创建json值的函数

5.1.1 json_array

5.1.2 json_object

5.1.3 JSON_QUOTE

5.2 检索json数据的值

5.2.1 JSON_CONTAINS

5.2.2 JSON_CONTAINS_PATH

5.2.3 JSON_EXTRACT

5.2.4 column->path

5.2.5 JSON_UNQUOTE

5.2.6 JSON_KEYS

5.2.7 JSON_SEARCH

5.2.8 JSON_VALUE

5.3 修改json数据的值

5.3.1 JSON_ARRAY_APPEND

5.3.2 JSON_ARRAY_INSERT

5.3.3 JSON_INSERT

5.3.4 JSON_MERGE

5.3.5 JSON_MERGE_PATCH

5.3.6 JSON_MERGE_PRESERVE

5.3.7 JSON_REMOVE

5.3.8 JSON_REPLACE

5.3.9 JSON_SET

5.3.10

5.4 返回JSON值属性的函数

5.4.1 JSON_DEPTH

5.4.2 JSON_LENGTH

5.4.3 JSON_TYPE

5.5 json table函数

5.6 json实用函数

5.6.1 JSON_PRETTY

参考: