如何用SQL查询将MariaDB传感器数据按时间戳分组导出为指定JSON格式？

现有遗留MariaDB表HistoricalData存储温湿度传感器数据，结构及示例数据如下：

需要将数据转换为如下格式的JSON数组：

[{
  "ts": "2021-11-10 08:08:13",
  "temp": 21.3,
  "hum": 52.7
}, 
{
  "ts": "2021-11-10 09:08:13",
  "temp": 23.3,
  "hum": 57.7
}]

你之前尝试的SQL未成功，原因是没有对同时间点的数据进行分组聚合，导致每条记录单独生成一个JSON对象，且每个对象中只有对应tag的字段有值，另一个为NULL。

解决方案 #

可以通过分组聚合+JSON函数实现需求，具体SQL如下：

SELECT JSON_ARRAYAGG(
    JSON_OBJECT(
        'ts', `date`,
        'temp', MAX(CASE WHEN tag = 'temp' THEN value END),
        'hum', MAX(CASE WHEN tag = 'hum' THEN value END)
    )
) AS result_json
FROM HistoricalData
GROUP BY `date`;

逻辑说明：

1. GROUP BY date：将同一时间点的温湿度数据归为一组
2. MAX(CASE...)：在每组中分别提取temp和hum对应的数值（每组中每个tag仅一条数据，用MAX/MIN/SUM均可实现提取效果）
3. JSON_OBJECT：将每个时间点的ts、temp、hum组合成单个JSON对象
4. JSON_ARRAYAGG：将所有时间点的JSON对象聚合成一个JSON数组

兼容低版本MariaDB（10.5以下） #

如果你的MariaDB版本低于10.5（不支持JSON_ARRAYAGG），可以用GROUP_CONCAT拼接JSON对象，再手动包裹数组格式：

SELECT CONCAT(
    '[',
    GROUP_CONCAT(
        JSON_OBJECT(
            'ts', `date`,
            'temp', MAX(CASE WHEN tag = 'temp' THEN value END),
            'hum', MAX(CASE WHEN tag = 'hum' THEN value END)
        )
        SEPARATOR ','
    ),
    ']'
) AS result_json
FROM HistoricalData
GROUP BY `date`
WITH ROLLUP
HAVING `date` IS NOT NULL;

注：若数据量较大，可能需要调整group_concat_max_len参数以避免拼接截断。

内容的提问来源于stack exchange，提问作者HappyTaco