Extract data using SQL in JSON format grouping by timestamp-CodePudding

I have a legacy MariaDB database that saves the historical data of our temperature and humidity sensors in the following way

id	date	value	tag
1	2021-11-10 08:08:13	21.3	temp
2	2021-11-10 08:08:13	52.7	hum
3	2021-11-10 09:08:13	23.3	temp
4	2021-11-10 09:08:13	57.7	hum

I want to extract this data in JSON with the following format:

[{
ts: 2021-11-10 08:08:13,
temp: 21.3,
hum: 52.7
}, 
{
ts: 2021-11-10 09:08:13,
temp: 23.3,
hum: 57.7
}]

Is it possible to do so with a SQL Query?

I have tried different combinations of Queries like this one but none have worked.

SELECT JSON_OBJECT(
  'ts', date, 
   'data', JSON_OBJECT(
  'temp', CASE WHEN tag ='temp' THEN value END, 
  'hum', CASE WHEN tag ='hum' THEN value  END  
  )    
  )
AS my_json FROM HistoricalData

Thank you in advance

CodePudding user response：

Query:

select 
  JSON_ARRAYAGG(
    JSON_OBJECT(
      'ts', t1.date, 'ts', t2.date, 'temp', 
      tValue, 'hum', hValue
    )
  ) 
from 
  (
    SELECT 
      date, 
      value as "tValue" 
    from 
      HistoricalData 
    where 
      tag = "temp"
  ) t1 
  join (
    SELECT 
      date, 
      value as "hValue" 
    from 
      HistoricalData 
    where 
      tag = "hum"
  ) t2 on t1.date = t2.date;

Result:

[
   {
      "ts":"2021-11-10 08:08:13.000000",
      "hum":52.70000076293945,
      "temp":21.299999237060547
   },
   {
      "ts":"2021-11-10 09:08:13.000000",
      "hum":57.70000076293945,
      "temp":23.299999237060547
   }
]