Given the following db structure https://drawsql.app/sensor_network/diagrams/db, I would like to get all sensor_data from a certain location, while grouping the response using station.location, sensor_data.time.

I have the following query:

select 
  station.location_name,
  sensor_data.time,
  sensor.type,
  sensor_data.value,
  sensor.id
from station
  inner join 
    (sensor inner join sensor_data on sensor.id = sensor_data.sensor_id)
  on station.sensor_id = sensor.id
where station.location = ?

which gives me the rows I want, however, I would like to format the response in a way where all the rows which share location and time are in the same row. an example output is

which I would like to format either like (or similar to)

[
{"location": "city1", "time": "2022-01-01 08:00:00", "temperature": 290, "ph": 7},
{"location": "city1", "time": "2022-01-01 09:00:00", "temperature": 292, "ph": 8},
]
or
[
{"location": "city1", "time": "2022-01-01 08:00:00", "sensors": [{"id": 1, "type": "temperature", "value": 290}, {"id": 2, "type": "ph", "value": 7}]},
{"location": "city1", "time": "2022-01-01 09:00:00", "sensors": [{"id": 1, "type": "temperature", "value": 292}, {"id": 2, "type": "ph", "value": 8}]}
]

Currently, I am doing this formatting in javascript using array functions, but this has proven to be too slow. I have tried using group by query but to be fair, I am struggling to understand how to use that when you don't want to use methods like count etc.

I am using nodejs and its mysql package, the database is mysql:8 and tables are running with innodb engine

CodePudding user response：

You can achieve this with subqueries.

SELECT 
  st.location,
  sd.time,
  (
    SELECT sd2.value
    FROM sensor_data sd2
    INNER JOIN sensor s2
      ON s2.id = sd2.sensor_id
    WHERE sd2.time = sd.time
      AND s2.type = "temperature"
  ) AS temperature,
  (
    SELECT sd2.value
    FROM sensor_data sd2
    INNER JOIN sensor s2
      ON s2.id = sd2.sensor_id
    WHERE sd2.time = sd.time
      AND s2.type = "ph"
  ) AS ph
FROM station st
  INNER JOIN sensor s
    ON st.sensors = s.id
  INNER JOIN sensor_data sd
    ON s.id = sd.sensor_id
WHERE st.location = ?
GROUP BY sd.time

Obviously, this will work only if you know the list of types (temperature, ph) in advance and thus you can write separate subqueries for each of them.

If you don't want to build separate subquery for each type, you can concat the values into a single subquery column.

SELECT 
  st.location,
  sd.time,
  (
    SELECT 
      GROUP_CONCAT(
        CONCAT(s2.type, ':', sd2.value)
        SEPARATOR ','
      )
    FROM sensor_data sd2
    INNER JOIN sensor s2
      ON s2.id = sd2.sensor_id
    WHERE sd2.time = sd.time
    GROUP BY sd2.time
  ) AS sensors
FROM station st
  INNER JOIN sensor s
    ON st.sensors = s.id
  INNER JOIN sensor_data sd
    ON s.id = sd.sensor_id
WHERE st.location = ?
GROUP BY sd.time;