MENU

MySQL JSON字段求和

2021 年 12 月 30 日 • 阅读: 898 • 编码,MySQL

注意: 此用法仅支持 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 协议》,转载必须注明作者和本文链接