mysql怎么直接显示json数据,如何将MySQL数据读取为JSON

I have a database table like this:

Then I want to read data as json object like this:

{

"date_time":"02102019",

"ma_vi_tri":

{

"1a":222,

"0a":111,

"2a":333

}

}

I use this SQL command like this:

MariaDB [mqtt]> SELECT json_object('date_time',date_time,'ma_vi_tri',ma_vi_tri, 'PH', PH) FROM PH where date_time='02102019';

But result output not like I wish.

解决方案

One option (be careful with performance problems):

SELECT

CONCAT(

'{"date_time": "', `date_time`, '", "ma_vi_tri": ',

REPLACE(

GROUP_CONCAT(

JSON_OBJECT(`ma_vi_tri`, `PH`)

),

'},{',

', '

),

'}'

) `JSON`

FROM

`PH`

WHERE

`date_time` = '02102019'

GROUP BY

`date_time`;