生成嵌套JSON

Question

我试图从一个简单的JSON生成一个嵌套的JSON。但我能找到解决办法。

这是我要转换的JSON:

[
{
"station_id": "ESP0001",
"datetime": "2022-11-25T06:43:06.000Z",
"dimension": "temperature",
"value": 15.33,
"representation": "°C",
"unit": "Celsius",
"unit_id": 1
},
{
"station_id": "ESP0001",
"datetime": "2022-11-25T06:43:06.000Z",
"dimension": "humidity",
"value": 92,
"representation": "%",
"unit": "Percentage",
"unit_id": 4
},
{
"station_id": "ESP0001",
"datetime": "2022-11-25T06:43:06.000Z",
"dimension": "pressure",
"value": 1016,
"representation": "hPa",
"unit": "HectoPascal",
"unit_id": 5
}
]

数据是由这个sql查询给出的(在MariaDB 10.8上):

SELECT  
r.station_id, 
r.datetime,
ud.dimension,
r.value,
u.representation,
u.unit,
r.unit_id
FROM readings r 
INNER JOIN units u ON (u.id = r.unit_id) 
INNER JOIN unit_dimension ud ON (ud.id=u.dimension_id)
WHERE r.station_id = 'ESP0001'
AND r.datetime >= (
SELECT max(datetime) 
FROM readings r2 
WHERE r2.unit_id = r.unit_id)
ORDER BY r.`datetime` DESC, u.id;

这是期望的JSON结构:

[
{
"station_id": "ESP0001",
"datetime": "2022-11-25T06:43:06.000Z",
"readings": [
{
"dimension": "temperature",
"value": 15.33,
"representation": "°C",
"unit": "Celsius",
"unit_id": 1
},
{
"dimension": "humidity",
"value": 92,
"representation": "%",
"unit": "Percentage",
"unit_id": 4
},
{
"dimension": "pressure",
"value": 1016,
"representation": "hPa",
"unit": "HectoPascal",
"unit_id": 5
}
]
}
]

这是de数据库的表:

• Units_dimension。
• 。
• 。

Units_dimension表:

<表类>id维tbody><<tr>1温度2湿度3压力

您可以首先通过对station_id和datetime进行分组来创建"readings"键聚合，然后再次执行json_arrayagg以构建最终结果:

select json_arrayagg(json_object("station_id", t.station_id, 
"datetime", t.datetime, "readings", t.js)) 
from (select r.station_id, r.datetime, json_arrayagg(json_object("dimension", ud.dimension, 
"value", r.value, 
"representation", u.representation, 
"unit", u.unit, "unit_id", u.id)) js 
from readings r join units u on r.unit_id = u.id 
join units_dimension ud on ud.id = u.dimension_id
group by r.station_id, r.datetime
order by r.datetime, r.unit_id) t

看到小提琴。

也可以看到同样的结果，这次用json_detailed修饰。