I'm trying to generate a nested JSON from a simple JSON. But I can find a solution.
This is the JSON that I try to convert:
[
{
"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
}
]
The data is given by this sql query (on 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;
This is the desired JSON structure:
[
{
"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
}
]
}
]
This are the tables of de database:
Units_dimension table:
| id | dimension |
|---|---|
| 1 | temperature |
| 2 | humidity |
| 3 | pressure |
Units table:
| id | dimension_id | unit | representation |
|---|---|---|---|
| 1 | 1 | Celsius | °C |
| 2 | 1 | Farenheit | °F |
| 3 | 1 | Kelvin | K |
| 4 | 2 | Percentage | % |
| 5 | 3 | HectoPascal | hPa |
Readings table:
| id | station_id | datetime | unit_id | value |
|---|---|---|---|---|
| 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 |
| ... | ... | ... | ... | ... |
The backend uses Nodejs.
I tried to use JSON functions without a valid solution.
With this query:
SELECT JSON_ARRAYAGG(
JSON_OBJECT(
'station_id', station_id,
'dt', datetime,
ud.dimension, value,
'representation', u.representation,
'unit', u.unit
)
) AS readings
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 station_id = 'ESP0001'
group by r.`datetime`
order by r.`datetime` desc, ud.id limit 1;
The result:
[
{"station_id": "ESP0001", "dt": "2022-11-25 07:43:06", "humidity": 92, "representation": "%", "unit": "Percentage"},
{"station_id": "ESP0001", "dt": "2022-11-25 07:43:06", "temperature": 15.33, "representation": "°C", "unit": "Celsius"},
{"station_id": "ESP0001", "dt": "2022-11-25 07:43:06", "pressure": 1016, "representation": "hPa", "unit": "HectoPascal"}
]
The only solution I can think of is to make two queries. One getting the last datetime and another to get the readings by station_id and datetime and make a mixed object.
Thanks!
UPDATE:
With @Ajax1234 solution query (I made some changes):
select json_arrayagg(
json_object(
"station_id", t.station_id,
"datetime", t.datetime,
"readings", t.js
)
)as "read"
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", r.unit_id
)
) js
from readings r
left join units u on u.id = r.unit_id
left join unit_dimension ud on ud.id = u.dimension_id
WHERE r.station_id = '${req.params.station_id}'
group by r.station_id, r.datetime
order by r.datetime DESC, r.unit_id ASC
LIMIT 20
) t;
This is the result:
[
{
"read": [
{
"station_id": "ESP0001",
"datetime": "2022-11-28 02:08:46",
"readings": [
{
"dimension": "temperature",
"value": 12.65,
"representation": "°C",
"unit": "Celsius",
"unit_id": 1
},
{
"dimension": "humidity",
"value": 79,
"representation": "%",
"unit": "Percentage",
"unit_id": 4
},
{
"dimension": "pressure",
"value": 1017,
"representation": "hPa",
"unit": "HectoPascal",
"unit_id": 5
}
]
},
{
"station_id": "ESP0001",
"datetime": "2022-11-28 01:58:46",
"readings": [
{
"dimension": "pressure",
"value": 1017,
"representation": "hPa",
"unit": "HectoPascal",
"unit_id": 5
},
{
"dimension": "humidity",
"value": 78,
"representation": "%",
"unit": "Percentage",
"unit_id": 4
},
{
"dimension": "temperature",
"value": 12.65,
"representation": "°C",
"unit": "Celsius",
"unit_id": 1
}
]
},
{
"station_id": "ESP0001",
"datetime": "2022-11-28 01:48:46",
"readings": [
{
"dimension": "temperature",
"value": 12.74,
"representation": "°C",
"unit": "Celsius",
"unit_id": 1
},
{
"dimension": "humidity",
"value": 77,
"representation": "%",
"unit": "Percentage",
"unit_id": 4
},
{
"dimension": "pressure",
"value": 1017,
"representation": "hPa",
"unit": "HectoPascal",
"unit_id": 5
}
]
},
{
"station_id": "ESP0001",
"datetime": "2022-11-28 01:38:46",
"readings": [
{
"dimension": "pressure",
"value": 1017,
"representation": "hPa",
"unit": "HectoPascal",
"unit_id": 5
},
{
"dimension": "humidity",
"value": 76,
"representation": "%",
"unit": "Percentage",
"unit_id": 4
},
{
"dimension": "temperature",
"value": 12.93,
"representation": "°C",
"unit": "Celsius",
"unit_id": 1
}
]
}
]
}
]
Sometimes is doing the ASC order but other times is doing DESC order.
You can first create the "readings" key aggregation by grouping on the station_id and datetime, and then perform json_arrayagg again to build the final result:
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
Also see this fiddle for the same results, this time prettified with json_detailed.