mySQL json result for functions

I have the following mySQL query that is working fine —

SELECT avg(age), avg(length)
FROM items

I need to produce the result in JSON. i tried the following along with some other queries with no success — from How to convert result table to JSON array in MySQL

SELECT JSON_ARRAYAGG(JSON_OBJECT('avg_age', avg(age), 'avg_l', avg(length)))  
FROM items

how can this be done?

TIA.

////////////////////

UPDATE: to get the result in jdbc later on — add an alias to the result —

SELECT JSON_OBJECT('avg_age', avg(age), 'avg_l', avg(length))  as aa
FROM items

then

resultSet.getString("aa");

these on top of the accepted result

Answer

You don’t need to use JSON_ARRAYAGG(). AVG() is doing the aggregation already, you just need JSON_OBJECT() to put the results in JSON.

SELECT JSON_OBJECT('avg_age', avg(age), 'avg_l', avg(length))
FROM items

Leave a Reply

Your email address will not be published. Required fields are marked *