备注：

版本: 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"} );

MySQL处理Json数据

三. json数据update

代码:

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

测试记录:

MySQL处理Json数据

四.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;

测试记录:

MySQL处理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;

测试记录:

MySQL处理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>