如何将MySQL数据读取为JSON [英] How to read MySQL data as JSON

问题描述

我有一个像这样的数据库表:

I have a database table like this:

然后我想像这样将数据读取为json对象:

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

{
   "date_time":"02102019",
   "ma_vi_tri":
   {
       "1a":222,
       "0a":111,
       "2a":333
   }
 }

我这样使用此SQL命令:

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`;

请参见 dbfiddle .

这篇关于如何将MySQL数据读取为JSON的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！