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.
• Units.
• Readings.

Units_dimension table:

Units table:

Readings table:

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!

CodePudding user response：

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) t

See fiddle.

Also see this fiddle for the same results, this time prettified with json_detailed.