MySQL aggregated sum of JSON objects

Question

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 1

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.

Answer 2

How about creating your own mysql function?

create function x_json_sum (json_object varchar(5000), json_path varchar(5000))
returns int deterministic
begin
  declare output int default 0;
  declare i int default 0;
  while json_extract(json_object, concat(json_path, '[', i,']')) is not null do
    set output = output + json_extract(json_object, concat(json_path, '[', i,']'));
    set i = i + 1;
  end while;
  return output;
end;

Result

MariaDB [****]> select x_json_sum('{"money": [3, 4, 3]}', '$.money');
+-----------------------------------------------+
| x_json_sum('{"money": [3, 4, 3]}', '$.money') |
+-----------------------------------------------+
|                                            10 |
+-----------------------------------------------+
1 row in set (0.00 sec)

I just wrote this function, and it works with my example. You might have to do some more validation like, when to return null? will data ever be truncated? function security and definer? etc.

You could also combine this sum function and create others such as x_json_avg etc.

Answer 3

It's not clear why you are using JSON at all, given that the data appears to have the same columns in every instance. You have not described any reason why you can't use traditional columns instead of JSON.

The JSON array should be stored in multiple rows of a second table.

CREATE TABLE `SampleDetails` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `sample_id` INT NOT NULL,
  `name` VARCHAR(10) NOT NULL,
  `amount` DECIMAL(9,2) NOT NULL,
  `percentage` TINYINT NOT NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO SampleDetails (sample_id, name, amount, percentage) VALUES
(1, 'T1', 34.34, 45},
(3, 'T3', 30.34, 45},
(2, 'T2', 14.34, 15}
(1, 'T1', 34.34, 45},
(2, 'T2', 30.34, 45},
(4, 'T4', 14.34, 15);

Then you can use very simple SQL to get the sums:

SELECT sample_id, MAX(name) AS name, 
  SUM(amount) AS amount, MAX(percentage) AS percentage
FROM SampleDetails
GROUP BY sample_id;

Fetch the result of that SQL query into your application and then format it as JSON. Every language has a package for reading and writing JSON.