MySql get rows into single JSON object

Question

I have table:

 -------+-------
| param | value |
|-------+-------|
|     A |  200.5|
|     B |   70.2|
 ---------------

When I execute:

select json_object(`param`, `value`) from `table`

I'm getting this:

{"A": "200.5"}
{"B": "70.2"}

But I want this:

{
 "A": "200.5",
 "B": "70.2"
}

Answer 1

You can use json aggregate function json_objectagg(), available since MySQL 5.7:

select json_objectagg(param, value) js from mytable

Demo on DB Fiddle:

| js                      |
| :---------------------- |
| {"A": 200.5, "B": 70.2} |

On earlier versions, where json aggregate functions are not available, you could do string concatenation:

select concat('{', group_concat('"', param, '": ', value separator ', '), '}') js
from mytable

Demo on DB Fiddle