Avarage of json array values in mysql

Question

I have label - its array of strings value (mysql json type) like: ["foo", "bar"] and I would like to get avarage for those values like: Example 1

ID | LABEL
1  | ["foo"]
2  | ["foo"]
3  | ["foo"]
4  | ["foo"]

Output: foo: 100%

Example 2

ID | LABEL
1  | ["foo"]
2  | ["foo"]
3  | ["bar"]
4  | ["bar"]

Output: foo: 50% Output: bar: 50%

Example 3

ID | LABEL
1  | ["foo", "bar"]
2  | ["foo", "bar"]

Output: foo: 50% Output: bar: 50%

How can I do it with single sql?

its possible?

I don't know which code should I use.

score 0 · Accepted Answer · answered Jul 26 '21 at 13:55

First what you need to do is to convert the items from the JSON array to rows.

You can do that with the JSON_TABLE MySQL function:

SELECT *
FROM json_table('["foo", "bar"]', '$[*]' COLUMNS(item text PATH '$')) AS jt;

The resultset looks like the following:

+------+
| item |
+------+
| foo  |
| bar  |
+------+

The next problem is to calculate the distribution of these values from the resultset.

SELECT item,
       count(*) * 100.0 / sum(count(*)) OVER () AS percentage
FROM json_table('["foo", "bar"]', '$[*]' COLUMNS(item text PATH '$')) AS jt
GROUP BY item;

The resultset will look like this:

+------+------------+
| item | percentage |
+------+------------+
| bar  |   50.00000 |
| foo  |   50.00000 |
+------+------------+

The final step is to join with the table:

SELECT item,
       count(*) * 100.0 / sum(count(*)) OVER () AS percentage
FROM your_table,
     json_table(label, '$[*]' COLUMNS(item text PATH '$')) AS jt
GROUP BY item;

And the result should be what you expected.

Avarage of json array values in mysql

1 Answers1