CODEWITHSUNDEEP
mysqljsonobjectmysql-5.5
iMarh
iMarh

Reputation: 188

MySql get rows into single JSON object

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

Upvotes: 1

Views: 1403

Answers (2)

GMB
GMB

Reputation: 222462

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

Upvotes: 3

Adrian Soomro
Adrian Soomro

Reputation: 46

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.

Upvotes: 0

Related Questions