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"
}

GMB · Accepted Answer · 2019-11-25T15:00:26.773

3

select json_objectagg(param, value) js from mytable

| 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

edited Nov 25 '19 at 15:00

answered Nov 25 '19 at 14:50

GMB

Thank you again GMB, but the server which I use is 5.5 ;-( – iMarh Nov 25 '19 at 14:57
@iMarh: I updated my answer with a solution for earlier versions. – GMB Nov 25 '19 at 15:00

score 0 · Answer 2 · answered Nov 25 '19 at 14:57

I assume you wan't to get this result directly from the MySQL server, if that's the case, the closest to an answer comes this How to convert result table to JSON array in MySQL.

If you're ok with getting the results further down the line you can always parse it using a language of your choice.

Hope that helps.

2 Answers2