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

please provide sample data and desired output – eshirvana Mar 10 '21 at 21:31 — eshirvana, Mar 10 '21 at 21:31

score 2 · Accepted Answer · answered Mar 10 '21 at 21:35

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 '21 at 21:35

Barmar

cool. not letting me accept yet-- will in 4mins. – cloud Mar 10 '21 at 21:40
1

how do you extract the result in jdbc? i'm looking for something like `resultSet.getString("json");` got it -- deleting in a moment – cloud Mar 10 '21 at 21:52
No idea, I don't use JDBC. – Barmar Mar 10 '21 at 21:53
Why don't you just produce JSON in the application program instead of the query? – Barmar Mar 10 '21 at 21:53
why shd i - i dont need the json library elsewhere like that ,it'd also produce extra objects/burden – cloud Mar 10 '21 at 21:58

1 Answers1