MySQL aggregated sum of JSON objects

I have created new table and having details as JSON datatype. I tried to get the aggregated sum of all records. I can able to get the each values but I don’t know how to get the sum using group by options.

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

Sample Data

 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"}]

I want the aggregated group of these 2 records

Output data

   [{"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"}]

I tried using JSON_EXTRACT(details, “$[*]”) but doesn’t work out

Answer

Update: Okay

First, I would definitely recommend normalizing the data a bit. Have you tried storing just the objects into the details column? If you needed to store groups of data with each Sample id, you can use a relating table. IE:)

Sample

id int auto increment

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

Details

sample_id int record json

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

Populate your data

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"}');

Then you can do something like

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 

Results

+---------------------------------------------------------------------+
| (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}                        |
+---------------------------------------------------------------------+

If you don’t want to (or can’t) use a normalized dataset, then perhaps you might look into a writing a stored procedure that loops over your details columns and aggregates the data for each one, with a query that aggregates the two datasets.