MySQL——JSON类型

骑猪看日落 2023-09-30 22:24 25阅读 0赞

### 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 
    );

#### 插入 ####

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;

![在这里插入图片描述][a87beb37cc5f436a8f3ee69fcd6494de.png]

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

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

![在这里插入图片描述][56be2fe620b2463b9cc9f29989b1fd04.png]

也可以使用`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;

![在这里插入图片描述][076d5f2c97d84a55af48aa4e62dbc587.png]

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

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

![在这里插入图片描述][632d6cac6024436ca88a87f7d847c2fd.png]

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

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

![在这里插入图片描述][ffe27970c5174c0691e324eb6fcf9fa4.png]

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

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

![在这里插入图片描述][c7e59f3d2739494f81ddb32a22a7e1b8.png]

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

![在这里插入图片描述][0caf91cfa5e94c79aec8795cb0491b43.png]

JSON\_LENGTH：  
给出JSON文档中的元素数：

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

![在这里插入图片描述][54b5de27158243a688f35e3f830b6148.png]

JSON\_ARRAY：  
创建JSON数组

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

![在这里插入图片描述][35d66900e23c4ec3aabdc42c629d7e39.png]

JSON\_OBJECT：  
创建JSON对象

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

![在这里插入图片描述][1cce98344f9a49ffb8c34f914a149ef8.png]

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

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

![在这里插入图片描述][6ca3254599d94203b07ecba1cfca7a52.png]

查找值：  
可以在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')

![在这里插入图片描述][2acf67188f8145e2881ae0e8c1e0ecbf.png]

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

![在这里插入图片描述][c38af4b6e1464e69aa6267df7fa54fe2.png]

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)

[a87beb37cc5f436a8f3ee69fcd6494de.png]: https://img-blog.csdnimg.cn/a87beb37cc5f436a8f3ee69fcd6494de.png
[56be2fe620b2463b9cc9f29989b1fd04.png]: https://img-blog.csdnimg.cn/56be2fe620b2463b9cc9f29989b1fd04.png
[076d5f2c97d84a55af48aa4e62dbc587.png]: https://img-blog.csdnimg.cn/076d5f2c97d84a55af48aa4e62dbc587.png
[632d6cac6024436ca88a87f7d847c2fd.png]: https://img-blog.csdnimg.cn/632d6cac6024436ca88a87f7d847c2fd.png
[ffe27970c5174c0691e324eb6fcf9fa4.png]: https://img-blog.csdnimg.cn/ffe27970c5174c0691e324eb6fcf9fa4.png
[c7e59f3d2739494f81ddb32a22a7e1b8.png]: https://img-blog.csdnimg.cn/c7e59f3d2739494f81ddb32a22a7e1b8.png
[0caf91cfa5e94c79aec8795cb0491b43.png]: https://img-blog.csdnimg.cn/0caf91cfa5e94c79aec8795cb0491b43.png
[54b5de27158243a688f35e3f830b6148.png]: https://img-blog.csdnimg.cn/54b5de27158243a688f35e3f830b6148.png
[35d66900e23c4ec3aabdc42c629d7e39.png]: https://img-blog.csdnimg.cn/35d66900e23c4ec3aabdc42c629d7e39.png
[1cce98344f9a49ffb8c34f914a149ef8.png]: https://img-blog.csdnimg.cn/1cce98344f9a49ffb8c34f914a149ef8.png
[6ca3254599d94203b07ecba1cfca7a52.png]: https://img-blog.csdnimg.cn/6ca3254599d94203b07ecba1cfca7a52.png
[2acf67188f8145e2881ae0e8c1e0ecbf.png]: https://img-blog.csdnimg.cn/2acf67188f8145e2881ae0e8c1e0ecbf.png
[c38af4b6e1464e69aa6267df7fa54fe2.png]: https://img-blog.csdnimg.cn/c38af4b6e1464e69aa6267df7fa54fe2.png