By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table units_dimension (id int, dimension text);
insert into units_dimension values (1, "temperature"), (2, "humidity"), (3, "pressure");
create table units (id int, dimension_id int, unit text, representation text);
insert into units values (1, 1, "Celsius", "°C"), (2, 1, "Farenheit", "°F"), (3, 1, "Kelvin", "K"), (4, 2, "Percentage", "%"), (5, 3, "HectoPascal", "hPa");
create table readings (id int, station_id text, datetime datetime, unit_id int, value float);
insert into readings values (1, "ESP0001", "2022-10-31 01:00:00.000", 1, 23.5), (2, "ESP0001", "2022-10-31 01:00:00.000", 4, 79), (3, "ESP0001", "2022-10-31 01:00:00.000", 5, 1019.6), (4, "ESP0001", "2022-10-31 02:00:00.000", 1, 23.3), (5, "ESP0001", "2022-10-31 02:00:00.000", 4, 79), (5, "ESP0001", "2022-10-31 02:00:00.000", 5, 1019.6)
Records: 3 Duplicates: 0 Warnings: 0
Records: 5 Duplicates: 0 Warnings: 0
Records: 6 Duplicates: 0 Warnings: 0
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_arrayagg(json_object("station_id", t.station_id, "datetime", t.datetime, "readings", t.js)) |
|---|
| [{"station_id": "ESP0001", "datetime": "2022-10-31 01:00:00", "readings": [{"dimension": "temperature", "value": 23.5, "representation": "°C", "unit": "Celsius", "unit_id": 1},{"dimension": "humidity", "value": 79, "representation": "%", "unit": "Percentage", "unit_id": 4},{"dimension": "pressure", "value": 1019.6, "representation": "hPa", "unit": "HectoPascal", "unit_id": 5}]},{"station_id": "ESP0001", "datetime": "2022-10-31 02:00:00", "readings": [{"dimension": "temperature", "value": 23.3, "representation": "°C", "unit": "Celsius", "unit_id": 1},{"dimension": "humidity", "value": 79, "representation": "%", "unit": "Percentage", "unit_id": 4},{"dimension": "pressure", "value": 1019.6, "representation": "hPa", "unit": "HectoPascal", "unit_id": 5}]}] |