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-25 15:00:26Z

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

answered Nov 25, 2019 at 14:50

GMB

224k25 gold badges103 silver badges151 bronze badges

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

2 Comments

Thank you again GMB, but the server which I use is 5.5 ;-(

@iMarh: I updated my answer with a solution for earlier versions.

Adrian Soomro · Accepted Answer · 2019-11-25 14:57:37Z

0

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.

answered Nov 25, 2019 at 14:57

Adrian Soomro

563 bronze badges