MySQL JSON字段求和

注意： 此用法仅支持 MySQL 8.0 以上的版本

我们假设一个场景，字段中存储一个日期的 JSON，这个 JSON 的 Key 为星期的英文名称，具体 JSON 格式如下：

{
    "monday": 390,
    "Tuesday": 240,
    "Wednesday": 150,
    "Thursday": 160,
    "Friday": 150,
    "Saturday": 270,
    "Sunday": 180
}

我们需要做的是对这些值进行取和，而 MySQL自身并不支持 JSON 值的聚合运算，但是我们可以通过 MySQL8 的新函数 JSON_TABLE 来的实现这个需求。

具体写法如下：

第一步，我们先拿到 JSON 的 VALUE 值

select * from JSON_TABLE('{"monday":390,"Tuesday":240,"Wednesday":150,"Thursday":160,"Friday":150,"Saturday":270,"Sunday":180}',"$.*" COLUMNS(
    rowid FOR ORDINALITY,
    price int PATH "$"
)) as temp

-- 查询结果如下，我们可以获取到所有的VALUE
+-------+
| price |
+-------+
|   150 |
|   180 |
|   390 |
|   240 |
|   270 |
|   160 |
|   150 |
+-------+
7 rows in set (0.03 sec)

第二步，上面 SQL 的基础上使用聚合函数 SUM() 来获取所有值的和：

select SUM(price) from JSON_TABLE('{"monday":390,"Tuesday":240,"Wednesday":150,"Thursday":160,"Friday":150,"Saturday":270,"Sunday":180}',"$.*" COLUMNS(
    rowid FOR ORDINALITY,
    price int PATH "$"
)) as temp

-- 聚合值输出结果如下：
+------------+
| SUM(price) |
+------------+
| 1540       |
+------------+

这样我们就可以拿到一个 JSON 格式的聚合结果，实际应用我们可以结合以下方案：

将聚合JSON 的 SQL 写成一个自定义函数，在MySQL 使用的时候直接传入 JSON 存储字段来简化查询
如果需要对 JSON 结果进行查询，那么使用JSON 函数的效率是极低的，可以参考我博客的上一篇文章，自定义函数结合 MySQL 的虚拟列来提高查询速度

CC版权：本篇博文采用《CC 协议》，转载必须注明作者和本文链接