MySql 获取行到单个 JSON 对象

Question

我有 table :

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

当我执行时:

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

我明白了:

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

但我想要这个:

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

Answer 1

您可以使用 json aggregate function json_objectagg() ，从 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