Grouping table columns having duplicates in mysql

Question

Am trying to group the information from a table called 'fruit_tb' below where the columns, 'cat_item' and 'ng_value' fall under their respective column 'ng_fy'; Below is the extracted table from MySQL.

cat_item	ng_fy	ng_value
Apples	2019/20	5
Mangoes	2019/20	14
Oranges	2019/20	3
Pears	2019/20	2
Apples	2020/21	11
Mangoes	2020/21	4
Oranges	2020/21	13
Pears	2020/21	0
Apples	2022/23	1
Mangoes	2022/23	4
Oranges	2022/23	18
Pears	2022/23	21

Below is the output am trying to achieve which will be in json format and such that i can render it on a bar graph (using chartjs).

2019/20 => [
    ["cat_item" => Apples,
    "ng_value" => 5],
    ["cat_item" => Mangoes,
    "ng_value" => 14],
    ["cat_item" => Oranges,
    "ng_value" => 3],
    ["cat_item" => Pears,
    "ng_value" => 2],
],
2020/21 => [
    ["cat_item" => Apples,
    "ng_value" => 11],
    ["cat_item" => Mangoes,
    "ng_value" => 4],
    ["cat_item" => Oranges,
    "ng_value" => 13],
    ["cat_item" => Pears,
    "ng_value" => 0],
],
2021/23 => [
    ["cat_item" => Apples,
    "ng_value" => 1],
    ["cat_item" => Mangoes,
    "ng_value" => 4],
    ["cat_item" => Oranges,
    "ng_value" => 18],
    ["cat_item" => Pears,
    "ng_value" => 21],
]

This is the query am using:

$query = "SELECT ng_fy, cat_item, ng_value FROM fruit_tb GROUP BY ng_fy, cat_item ORDER BY ng_fy ASC";

I then loop through the information using PHP. Except am not getting the desired results. Any help is appreciated.

Answer 1

MySQL offers JSON builder and aggregate functions that come handy here:

select ng_fy, 
    json_arrayagg(
        json_object(
            'cat_item', cat_item,
            'ng_value', ng_value
        )
    ) js  
from fruit_tb
group by ng_fy
order by ng_fy

Here, json_object() builds json objects that json_arrayagg() then aggregates. This returns a resultset with two columns, one that holds the date and the other the json array.