[SOLVED] MySQL convert Grouped JSON selection into a single JSON Object

MySQL convert Grouped JSON selection into a single JSON Object

I have a table

 ---------+-----------
| type_id | A | B | A |
 ---------+-----------
|   stock | 1 | 2 | 2 |
 ---------+-----------

I'm trying to run

select stock.type_id, sum(stock.stock)
from stock group by type_id;

Turning it into JSON object

I tried running

> select 
>       json_arrayagg( 
>          JSON_ARRAY(
>               'type_id', stock.type_id, 
>               'stock', sum(stock.stock)
>           )
>       )
>   from stock group by type_id ;

But it returns Error(1111): Invalid use of group function

I tried various types of concatenation, but it still returns various errors :-/

If used without json_arrayagg part, it returns multiple rows.

If used without "group by type_id", it returns a single JSON row, but the results are all wrong and it can't be used to return the sum just separate rows :-(

Solution

You can't put an aggregation inside another aggregation in the same query.

But you can run the SUM() query as a subquery, and then apply the JSON_ARRAYAGG() in an outer query:

SELECT JSON_ARRAYAGG(
  JSON_OBJECT(
    'type_id', t.type_id,
    'stock', t.stock_sum
  )
) AS `results`
FROM (
  SELECT type_id, SUM(stock) AS stock_sum
  FROM stock GROUP BY type_id
) AS t;