Extract key from JSON string in MySQL

Question

My table contains string in json format. I need to get the sum and average of each key.

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

Expected result is

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

I've tried

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

But no luck. How can I sort this out?

Are those supposed to be JSON arrays? If so, they need [] around them. — Barmar
– Barmar, Commented May 24, 2019 at 10:00
As far as I know, there's no easy way to extract the elements of a JSON array by a property value, and there's no way to group that way. — Barmar
– Barmar, Commented May 24, 2019 at 10:01
@Barmar sir, I read some references which says that Aliases sum_data, avg_data maps the JSON_OBJECT() — varman
– varman, Commented May 24, 2019 at 10:09

halfer · Accepted Answer · 2019-05-25 08:57:28Z

2

Input json needs to correct

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

Output:

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}

edited May 25, 2019 at 8:57

halfer

20.2k20 gold badges110 silver badges207 bronze badges

answered May 24, 2019 at 11:22

Raja vikraman

1697 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Extract key from JSON string in MySQL

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related