MySQL JSON 数据类型

1. JSON 类型

MySQL 从版本 5.7 开始正式支持 JSON 类型
The JSON Data Type 文档 https://dev.mysql.com/doc/refman/8.0/en/json.html

1.1 操作符

->（箭头操作符）
从 JSON 字段中提取值

->>（双箭头操作符）
与 -> 类似，也是从 JSON 字段中提取值，但关键区别在于它会将提取出的标量值转换为文本字符串

2. JSON 类型查询

2.1 JSONObject

+------------------------------------------------------------------------+
|                                json1                            |
+------------------------------------------------------------------------+
| {"type": {"sta1": 1, "sta2": 2}, "express": 1, "official": 0}  |
+------------------------------------------------------------------------+

--  $ 表示当前 JSON 数据
select json1 -> '$.express' from boot_indicator;

-- 等价：JSON_EXTRACT(json1, '$.express')
-- 提取JSON值：JSON_EXTRACT(column, path)

2.2 JSONArray

+------------------------------------------------------------------------------------+
|      json2     |          json 3                        |
+------------------------------------------------------------------------------------+
|  [1001, 1002]  |    [{"age": 22, "name": "tinyspot"}, {"age": 20}]   |
+------------------------------------------------------------------------------------+

select json2 ->> '$[0]' from boot_indicator;
select json2 ->> '$[1]' from boot_indicator;

-- 输出：{"age": 22, "name": "tinyspot"}
select json2 ->> '$[0]' from boot_indicator;
-- 输出：22
select json2 ->> '$[0].age' from boot_indicator;

2.3 通配符查询

官方文档 https://dev.mysql.com/doc/refman/8.0/en/json.html
通配符 * 和 **

select JSON_EXTRACT(json1, '$.*') from boot_indicator;
-- 输出：[{"sta1": 1, "sta2": 2}, 1, 0]
select json1 -> '$.*' from boot_indicator;
-- 输出：[1, 2]
select json1 -> '$.type.*' from boot_indicator;

select JSON_EXTRACT(json2, '$[*]') from boot_indicator;
-- [{"age": 22, "name": "tinyspot"}, {"age": 20}]
select json2 ->> '$[*]' from boot_indicator;
-- [22, 20]
select json2 ->> '$[*].age' from boot_indicator where id = 1;

3. JSON 函数

文档 https://dev.mysql.com/doc/refman/8.3/en/json-function-reference.html

3.1 查询

select id, json1, json2 from boot_indicator
where json1 ->> '$.name' = 'tinyspot';

SELECT id, json1, json2 FROM boot_indicator
WHERE JSON_CONTAINS(json1, '"tinyspot"', '$.name');

select id, json1, json2 from boot_indicator
where json_contains(json2 ->> '$[*].name', '"tinyspot"');

补充：注意双引号
select CONCAT('"', 'demo' , '"'); 输出 "demo"

-- 参数形式：json1 -> '$.name' LIKE CONCAT('%', #{typeName}, '%')
select id, json1, json2 from boot_indicator
where json1 -> '$.name' LIKE CONCAT('%', 'tinyspot', '%');

3.2 检索函数 JSON_CONTAINS()

语法 JSON_CONTAINS(target, candidate[, path])
返回值：1(真) 0(假)

-- JSONArray: [1001, 1002]
select json2 -> '$[*]' from boot_indicator;
select JSON_CONTAINS(json2 ->> '$[*]', '1001') from boot_indicator;
select JSON_CONTAINS(json2 ->> '$[*]', json_array(1001)) from boot_indicator;

-- json1: {"num1": 100, "text5": [1001, 1002], "text6": "307269"}
select json_contains(json1 ->> '$.text5', '1001') from boot_indicator;
select json_contains(json1 ->> '$.text5', json_array(1001)) from boot_indicator;
select id, json1 from boot_indicator where json_contains(json1 ->> '$.text5', json_array(1001));

-- json2: [{"age": 22, "name": "tinyspot"}, {"age": 20}]
-- $[*].name:  ["tinyspot"]
select json2 ->> '$[*].name' from boot_indicator where id = 1;

select JSON_CONTAINS(json2 ->> '$[*].name', '"tinyspot"') from boot_indicator;
-- '$' 路径前缀，表示在整个 JSON 文档范围内进行搜索
-- select JSON_CONTAINS(json2 ->> '$[*].name', '"tinyspot"', '$') from boot_indicator;
-- select JSON_CONTAINS(json2, '"tinyspot"', '$[0].name') from boot_indicator;

-- 参数形式
-- select * from boot_indicator where id = 1001 and JSON_CONTAINS(data->'$[*].name', CONCAT('"', #{param}, '"'), '$');

3.3 json_object()

json_object() 创建JSON 对象

-- {"name": "tinyspot"}
select json_object('name', 'tinyspot')

-- 查询
select id, json1, json2 from boot_indicator
where JSON_CONTAINS(json1, JSON_OBJECT('name', 'tinyspot'));

3.4 json_array()

-- ["aaa", "bbb"]
select json_array('aaa', 'bbb');

update boot_indicator
set json1 = json_object('text5', json_array('aaa', 'bbb'))
where id = 4;

update boot_indicator
set json1 = json_set(json1, '$.text5', json_array('aaa', 'bbb'))
where id = 3;

4. JSON 更新

4.1 JSONObject 字段更新

update boot_indicator
set json1 = json_set(json1, '$.name', 'demo')
where id = 2;

-- 批量更新
update boot_indicator 
set json1 = case
                WHEN id = 1 THEN json_set(json1, '$.name', 'demo1')
                WHEN id = 2 THEN json_set(json1, '$.name', 'demo2')
             end
WHERE id IN (1, 2);

4.2 JSONArray 字段更新

update boot_indicator
set json2 = json_set(json2, '$[0].name', 'demo1')
where id = 2;

-- 批量更新
update boot_indicator
set json2 = case
               when id = 1 then json_set(json2, '$[0].name', 'demo1')
               when id = 2 then json_set(json2, '$[0].name', 'demo2')
            end
where id in (1, 2);

-- JSONArray 里添加一个 JSONObject
update boot_indicator
set json2 = json_array_append(json2, '$', JSON_OBJECT('name', 'aaa'))
where id = 2;

-- 批量更新
update boot_indicator
set json2 =
        case
            when id = 1 then json_array_append(json2, '$', JSON_OBJECT('name', 'tinyspot1'))
            when id = 2 then json_array_append(json2, '$', JSON_OBJECT('name', 'tinyspot2'))
        end
where id in (1, 2);