太实用了！JSON在Mysql中原来可以这么玩

迈不过友情╰ 2022-11-17 10:20 236阅读 0赞

## 一、背景 ##

最近接手做一个医疗项目，其中有一个需求是给病人做统计报表，其实这个之前是有过一版的，只是上到线上就立刻崩了。崩溃的原因也很简单，前端接受到数据量达到了400-500M,然后还要前端对这些数据进行处理、渲染，不卡死才怪。前端进行数据处理的原因是：当时后端工作量大，数据没有处理直接一骨碌全部返回给前端，而且前端页面需要显示不同的统计图和表格，数据的处理方式不一样。

了解了原因之后，就要查看具体的后端代码了。这是一个新上线的服务，按道理数据量不会很大。于是我进一步查看后台逻辑，其实关联的表也不过5张，数据条数也不超过5000条，这个数据量，按道理是不会崩溃才对，然后进一步查看，其中一张表存了一个report字段，字段类型为json,这个字段奇大无比，而且后端解析的逻辑也很多，解析好后，还要把这个字段返回给前端，重复了。来感受一下这个字段的恐惧吧，算了，太长了，还是放在本文的末尾吧，不然估计你都没兴趣看下面的正文的内容了，如果有兴趣的，可以翻到本文末尾来体会一下绝望吧。

## 二、解决办法 ##

1.  查看原需求文档，定位出报表需要用到的字段
2.  第一个方案是将report字段分解，然后存到另外一张表，报表的时候再关联到这张分解好的表。但是这个需要一个定时任务来同步分解这个字段。后来查看mysql的文档看到了一个函数json\_extract，简直可以完美解决我目前遇到的这问题。那张大表我是无力改了，和业务深度关联，费时费力。通过sql的手段直接减少绝大部分的数据量。
3.  后端做报表数据分析处理，前端只需要渲染

## 三、json\_extract的使用方法 ##

1.  获取value为非集合类型：统计报表中有一个体质，其中对应report字段中`health_report.const_section.const_code`，那么怎么截取出来呢？

SELECT 
        json_extract(json_extract(json_extract(r.report,"$.health_report"),"$.const_section"),"$.const_code") as physique 
    from 
        `habitus_report` r;

![执行结果展示][38dff3bb8d14129175342bcdb150904f.png]  
2. 获取value为非集合类型：统计报表中的疾病类型，是report字段中`health_report.const_detail.disease_id`,这个疾病类型是一个数组，我需要的只是疾病id,实现代码如下：

select
        json_extract(json_extract(json_extract(json_extract(r.report,"$.health_report"),"$.const_detail"),"$.diseases"),"$[*].disease_id") as diseaseIds
    from
       `habitus_report` r;

![查询结果展示][ca0cd8c43c51580f65796404f538b232.png]  
截取完之后，返回给后端的数据节省了98%的大小，是不是很实用？

## 四、mysql中其它json处理函数用法简介 ##

### 4.1 创建json ###

1.  `JSON_OBJECT([key, val[, key, val] ...])`：创建json对象

mysql> SELECT JSON_OBJECT('key1', 1, 'key2', 'abc', 'key1', 'def') as jsonObj;
    +------------------------------------------------------+
    | jsonObj 
    +------------------------------------------------------+
    | {"key1": 1, "key2": "abc"}                           
    +------------------------------------------------------+

1.  `JSON_ARRAY([val[, val] ...])`：创建json数组

mysql> SELECT JSON_ARRAY('a', 1, NOW()) as jsonArr;
    +----------------------------------------+
    | jsonArr              
    +----------------------------------------+
    | ["a", 1, "2015-07-27 09:43:47.000000"] 
    +----------------------------------------+

### 4.2 修改json ###

1.  `JSON_SET(json_doc, path, val[, path, val] ...)`：有存在的数据就替换，没有就插入

SET @j = '["a", {"b": [true, false]}, [10, 20]]';
    mysql> SELECT JSON_SET(@j, '$[1].b[0]', 1, '$[2][2]', 2) as jsonSet;
    +--------------------------------------------+
    | jsonSet 
    +--------------------------------------------+
    | ["a", {"b": [1, false]}, [10, 20, 2]]      
    +--------------------------------------------+

1.  `JSON_INSERT(json_doc, path, val[, path, val] ...)`：在JSON数组后增加新的数据

SET @j = '["a", {"b": [true, false]}, [10, 20]]';
    mysql> SELECT JSON_INSERT(@j, '$[1].b[0]', 1, '$[2][2]', 2) as jsonInsert;
    +-----------------------------------------------+
    | JSON_INSERT(@j, '$[1].b[0]', 1, '$[2][2]', 2) |
    +-----------------------------------------------+
    | ["a", {"b": [true, false]}, [10, 20, 2]]      |

1.  `JSON_REMOVE(json_doc, path[, path] ...)`：删除原json中的值

SET @j = '["a", {"b": [true, false]}, [10, 20]]';
    --删除指标为2的值、删除指标为1里指标为1的值
    mysql> SELECT JSON_REMOVE(@j, '$[2]', '$[1].b[1]', '$[1].b[1]');
    +---------------------------------------------------+
    | JSON_REMOVE(@j, '$[2]', '$[1].b[1]', '$[1].b[1]') |
    +---------------------------------------------------+
    | ["a", {"b": [true]}]                              |

1.  `JSON_REPLACE(json_doc, path, val[, path, val] ...)`：替换一部分JSON数据

mysql> SELECT JSON_REPLACE(@j, '$[1].b[0]', 1, '$[2][2]', 2);
    +------------------------------------------------+
    | JSON_REPLACE(@j, '$[1].b[0]', 1, '$[2][2]', 2) |
    +------------------------------------------------+
    | ["a", {"b": [1, false]}, [10, 20]]             |
    +------------------------------------------------+

### 4.3 合并json数据 ###

1.  `JSON_REMOVE(json_doc, path[, path] ...)`：将两个json合并

mysql> SELECT JSON_MERGE('{"a": 1, "b": 2}', '{"c": 3, "a": 4}');
    +----------------------------------------------------+
    | JSON_MERGE('{"a": 1, "b": 2}', '{"c": 3, "a": 4}') |
    +----------------------------------------------------+
    | {"a": [1, 4], "b": 2, "c": 3}                      |
    +----------------------------------------------------+

### 4.4 查询json数据 ###

1.  `JSON_CONTAINS(json_doc, val[, path])`：是否包含
2.  `JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...)`：查找path（就是key）是否存在
3.  `JSON_EXTRACT(json_doc, path[, path] ...)`：分解JSON 并查询，实际上就是在提供的path下查找值
4.  `JSON_KEYS(json_doc[, path])`：提出当前提供path下的key值
5.  `JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])`：按着提供的值去查询，返回path数组。one\_or\_all南

### 4.5 查询JSON自有属性的方法 ###

1.  `JSON_DEPTH(json_doc)`：查询当前JSON深度
2.  `JSON_LENGTH(json_doc[, path])`：查询当前层级(path)下对象或者数组的元素数量
3.  `JSON_TYPE(json_val)`：返回JSON值类型
4.  `JSON_VALID(val)`：json校验

### 4.6 将其它类型转换为json类型 ###

1.  `CAST(value AS JSON)`：将其它类型转换为json类型

[38dff3bb8d14129175342bcdb150904f.png]: /images/20221022/a5a68dea32c34b8db7dc64dfbcf5153a.png
[ca0cd8c43c51580f65796404f538b232.png]: /images/20221022/27d69d5177104715a1b312a497f62cc6.png