MySQL JSON对象的总和

阿南达库玛河

我创建了新表，并将详细信息作为JSON数据类型。我试图获取所有记录的总和。我可以获取每个值，但我不知道如何使用group by选项获取总和。

CREATE TABLE `Sample` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `details` json DEFAULT NULL,
  PRIMARY KEY (`id`)
)  CHARSET=latin1;

样本数据

 1. [{"id": 1, "name": "T1", "amount": "34.34", "percentage": "45"}, {"id": 3, "name": "T3", "amount": "30.34", "percentage": "45"}, {"id": 2, "name": "T2", "amount": "14.34", "percentage": "15"}]

 2. [{"id": 1, "name": "T1", "amount": "34.34", "percentage": "45"}, {"id": 2, "name": "T2", "amount": "30.34", "percentage": "45"}, {"id": 4, "name": "T4", "amount": "14.34", "percentage": "15"}]

我想要这2条记录的汇总组

输出数据

   [{"id": 1, "name": "T1", "amount": "68.68", "percentage": "45"}, {"id": 3, "name"`enter code here`: "T3", "amount": "30.34", "percentage": "45"}, {"id": 2, "name": "T2", "amount": "44.68", "percentage": "60"}, {"id": 4, "name": "T4", "amount": "14.34", "percentage": "15"}]

我尝试使用JSON_EXTRACT（详细信息，“ $ [*]”），但无法解决

肖恩·格雷戈里

更新：好的

首先，我绝对建议您稍微标准化一下数据。您是否尝试过仅将对象存储到详细信息列中？如果需要使用每个样本ID存储数据组，则可以使用相关表。IE ：）

样品

id int自动递增

mysql> create table Sample (id int(11) not null auto_increment, primary key(id));

细节

sample_id int记录json

mysql> create table Details (sample_id int(11), record json);

填充数据

insert into Sample (id) values (1);
insert into Sample (id) values (2);

insert into Details (sample_id, record) values 
  (1, '{"id": 1, "name": "T1", "amount": "34.34", "percentage": "45"}'), 
  (1, '{"id": 3, "name": "T3", "amount": "30.34", "percentage": "45"}'), 
  (1, '{"id": 2, "name": "T2", "amount": "14.34", "percentage": "15"}');

insert into Details (sample_id, record) values 
  (2, '{"id": 1, "name": "T1", "amount": "34.34", "percentage": "45"}'),
  (2, '{"id": 2, "name": "T2", "amount": "30.34", "percentage": "45"}'),
  (2, '{"id": 4, "name": "T4", "amount": "14.34", "percentage": "15"}');

然后你可以做类似的事情

SELECT (
  JSON_OBJECT('id', id, 'amount', amount, 'percentage', percentage)
) FROM (
  SELECT 
    JSON_EXTRACT(record, "$.id") as id, 
    SUM(JSON_EXTRACT(record, "$.amount")) as amount, 
    AVG(JSON_EXTRACT(record, "$.percentage")) as percentage
  FROM Details 
  GROUP BY JSON_EXTRACT(record, "$.id")
) as t

结果

+---------------------------------------------------------------------+
| (JSON_OBJECT('id', id, 'amount', amount, 'percentage', percentage)) |
+---------------------------------------------------------------------+
| {"id": 1, "amount": 68.68, "percentage": 45}                        |
| {"id": 2, "amount": 44.68, "percentage": 30}                        |
| {"id": 3, "amount": 30.34, "percentage": 45}                        |
| {"id": 4, "amount": 14.34, "percentage": 15}                        |
+---------------------------------------------------------------------+

如果您不想（或不能）使用规范化的数据集，那么您可能会考虑编写一个存储过程，该存储过程遍历您的详细信息列并汇总每个数据，并通过查询汇总两个数据数据集。

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。