如何编写MySQL查询汇总JSON字段中各box的books与pen数值？

架构老林

2026-7-23

MySQL JSON字段分组汇总查询方案

问题背景

现有user_data表结构如下：

CREATE TABLE `user_data` (
  `id` int(11) NOT NULL,
  `user` varchar(100) NOT NULL,
  `data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`data`))
)

表中data字段为JSON格式，存储以box为键、包含books和pen数值的嵌套数据，表内数据示例：

id	user	data
1	1	{"box1": {"books": 12, "pen": 100},"box2": {"books": 13, "pen": 200},"box4": {"books": 17, "pen": 300},"box5": {"books": 16, "pen": 300}}
2	5	{"box1": {"books": 12, "pen": 100},"box2": {"books": 13, "pen": 200},"box3": {"books": 14, "pen": 300}}

需要实现按box分组，汇总对应books和pen的总和，期望结果：

box	books	pen
box1	24	200
box2	26	400
box3	14	300
box4	17	300
box5	16	300

解决方案

可以利用MySQL的JSON函数JSON_TABLE将JSON对象拆分为行数据，再进行分组汇总，查询语句如下：

SELECT
    j.box_name AS box,
    SUM(j.books) AS books,
    SUM(j.pen) AS pen
FROM
    user_data,
    JSON_TABLE(
        JSON_KEYS(data),
        '$[*]' COLUMNS(
            box_name VARCHAR(100) PATH '$',
            books INT PATH CONCAT('$.', '$', '.books'),
            pen INT PATH CONCAT('$.', '$', '.pen')
        )
    ) AS j
GROUP BY
    j.box_name
ORDER BY
    j.box_name;