mySQL json result for functions

Question

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

Barmar · Accepted Answer · 2021-03-10 21:35:46Z

2

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

answered Mar 10, 2021 at 21:35

Barmar

789k57 gold badges554 silver badges669 bronze badges

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

5 Comments

cool. not letting me accept yet-- will in 4mins.

how do you extract the result in jdbc? i'm looking for something like resultSet.getString("json"); got it -- deleting in a moment

No idea, I don't use JDBC.

Why don't you just produce JSON in the application program instead of the query?

why shd i - i dont need the json library elsewhere like that ,it'd also produce extra objects/burden