extract data from sql query json format

Question

I have a table sensor_measurements and the columns measurements and measure_at (timestamp)

 select measured_at, pollutants
    from sensor_measurements;

which gives:

October 22, 2019, 9:00 PM
[{"name": "NO", "units": "ppm", "concentration": 0.002161, "temporal_resolution": "1h"},
 {"name": "NO2", "units": "ppm", "concentration": 0.002, "temporal_resolution": "1h"},
  {"name": "TEMP", "units": "celsius", "concentration": 28, "temporal_resolution": "1h"},
   {"name": "HUM", "units": "percent", "concentration": 38, "temporal_resolution": "1h"}, 
   {"name": "PM10", "units": "µg/m3", "concentration": 8, "temporal_resolution": "1h"},
    {"name": "PM25", "units": "µg/m3", "concentration": 7, "temporal_resolution": "1h"}]
    
October 22, 2019, 10:00 PM
[{"name": "NO", "units": "ppm", "concentration": 0.002205, "temporal_resolution": "1h"},
 {"name": "NO2", "units": "ppm", "concentration": 0.008, "temporal_resolution": "1h"},
  {"name": "TEMP", "units": "celsius", "concentration": 28, "temporal_resolution": "1h"}, 
  {"name": "HUM", "units": "percent", "concentration": 38, "temporal_resolution": "1h"}, 
  {"name": "PM10", "units": "µg/m3", "concentration": 9, "temporal_resolution": "1h"}, 
  {"name": "PM25", "units": "µg/m3", "concentration": 8, "temporal_resolution": "1h"}]
  
October 22, 2019, 11:00 PM
[{"name": "NO", "units": "ppm", "concentration": 0.002209, "temporal_resolution": "1h"},
 {"name": "NO2", "units": "ppm", "concentration": 0.004, "temporal_resolution": "1h"},
  {"name": "TEMP", "units": "celsius", "concentration": 28, "temporal_resolution": "1h"}, 
  {"name": "HUM", "units": "percent", "concentration": 38, "temporal_resolution": "1h"}, 
  {"name": "PM10", "units": "µg/m3", "concentration": 8, "temporal_resolution": "1h"}, 
  {"name": "PM25", "units": "µg/m3", "concentration": 7, "temporal_resolution": "1h"}]
  
October 23, 2019, 12:00 AM
[{"name": "NO", "units": "ppm", "concentration": 0.002125, "temporal_resolution": "1h"},
{"name": "NO2", "units": "ppm", "concentration": 0.004, "temporal_resolution": "1h"}, 
{"name": "TEMP", "units": "celsius", "concentration": 28, "temporal_resolution": "1h"}, 
{"name": "HUM", "units": "percent", "concentration": 39, "temporal_resolution": "1h"}]


October 23, 2019, 4:00 PM
[{"name": "NO", "units": "ppm", "concentration": 0.004563, "temporal_resolution": "1h"}, 
{"name": "TEMP", "units": "celsius", "concentration": 34, "temporal_resolution": "1h"}, 
{"name": "HUM", "units": "percent", "concentration": 28, "temporal_resolution": "1h"}]

I want to extract the timestamp, pollutant and it's value (concentration!

Ideally , I want to create three columns with timestamp, pollutant and value in order to download as csv.

The database type is PostgreSQl (in metabase.com)

Answer 1

I did it on postgres.
First of all you have to have type represents your data:

CREATE TYPE x as ("name" VARCHAR , "units" VARCHAR , "concentration" FLOAT, "temporal_resolution" VARCHAR );

Next, you can use json as joined table:

SELECT measured_at, name, concentration
FROM sensor_measurements
LEFT JOIN LATERAL json_populate_recordset(null::x, pollutants::json) ON true;