我的表包含 json 格式的字符串。我需要得到每个键的总和和平均值。

+----+------------------------------------------------------------------------------------+------------+ 
| id | json_data                                                                          | subject_id | 
+----+------------------------------------------------------------------------------------+------------+ 
| 1  | {"id": "a", "value": "30"}, {"id": "b", "value": "20"}, {"id": "c", "value": "30"} | 1          | 
+----+------------------------------------------------------------------------------------+------------+ 
| 2  | {"id": "a", "value": "40"}, {"id": "b", "value": "50"}, {"id": "c", "value": "60"} | 1          | 
+----+------------------------------------------------------------------------------------+------------+ 
| 3  | {"id": "a", "value": "20"}                                                         | 1          | 
+----+------------------------------------------------------------------------------------+------------+ 

预期结果是

    {"id": "a", "sum": 90, "avg": 30}, 
    {"id": "b", "sum": 70, "avg": 35}, 
    {"id": "c", "sum": 120, "avg": 40} 

我试过了

SELECT ( 
  JSON_OBJECT('id', id, 'sum', sum_data, 'avg', avg_data) 
) FROM ( 
  SELECT  
    JSON_EXTRACT(json_data, "$.id") as id,  
    SUM(JSON_EXTRACT(json_data, "$.sum_data")) as sum_data,  
    AVG(JSON_EXTRACT(json_data, "$.avg_data")) as avg_data 
  FROM Details  
  GROUP BY JSON_EXTRACT(json_data, "$.id") 
) as t  

但运气不好。我该如何解决这个问题？

请您参考如下方法:

输入的json需要更正

create table json_sum (id int primary key auto_increment, json_data json); 
 
insert into json_sum values (0,'[{"id": "a", "value": "30"}, {"id": "b", "value": "20"}, {"id": "c", "value": "30"}]'); 
insert into json_sum values (0,'[{"id": "a", "value": "40"}, {"id": "b", "value": "50"}, {"id": "c", "value": "60"}]'); 
insert into json_sum values (0,'[{"id": "a", "value": "20"}]'); 
 
select  
 json_object("id", jt.id, "sum", sum(jt.value), "avg", avg(jt.value)) 
 from json_sum, json_table(json_data, "$[*]" columns ( 
        row_id for ordinality, 
        id varchar(10) path "$.id", 
        value varchar(10) path "$.value") 
) as jt 
group by jt.id 
 

输出:

json_object("id", jt.id, "sum", sum(jt.value), "avg", avg(jt.value)) 
{"id": "a", "avg": 30.0, "sum": 90.0} 
{"id": "b", "avg": 35.0, "sum": 70.0} 
{"id": "c", "avg": 45.0, "sum": 90.0}