1  INSERT INTO samples (time, event_name, source, type, metric_id, instance_index, value)
 2  SELECT
 3      events.time,
 4      events.name,
 5      events.source,
 6      events.type,
 7      metrics.id,
 8      metric_extraction.instance_index,
 9      metric_extraction.metric_value
10  FROM events
11  CROSS JOIN LATERAL (
12      SELECT
13          COALESCE((temperature_instance ->> 'instance_index')::int, 0) AS instance_index,
14          temperature_metric.metric_name,
15          temperature_metric.metric_value
16      FROM jsonb_array_elements(events.data -> 'instances') AS temperature_instance
17      CROSS JOIN LATERAL (
18          VALUES
19              ('temperature_celsius', (temperature_instance ->> 'temperature_celsius')::double precision),
20              ('relative_humidity_percent', (temperature_instance ->> 'relative_humidity_percent')::double precision)
21      ) AS temperature_metric(metric_name, metric_value)
22      WHERE events.type = 'sensors.temperature_and_humidity.v1'
23        AND jsonb_typeof(events.data -> 'instances') = 'array'
24  
25      UNION ALL
26  
27      SELECT
28          0 AS instance_index,
29          'wind_speed_kilometers_per_hour'::text AS metric_name,
30          (events.data ->> 'wind_speed_kilometers_per_hour')::double precision AS metric_value
31      WHERE events.type = 'sensors.wind_speed.v1'
32  
33      UNION ALL
34  
35      SELECT
36          0 AS instance_index,
37          wind_direction_metric.metric_name,
38          wind_direction_metric.metric_value
39      FROM (
40          VALUES
41              ('wind_direction_angle', (events.data ->> 'wind_direction_angle')::double precision),
42              ('wind_direction_slice', (events.data ->> 'wind_direction_slice')::double precision)
43      ) AS wind_direction_metric(metric_name, metric_value)
44      WHERE events.type = 'sensors.wind_direction.v1'
45  
46      UNION ALL
47  
48      SELECT
49          0 AS instance_index,
50          soil_metric.metric_name,
51          soil_metric.metric_value
52      FROM (
53          VALUES
54              ('temperature_celsius', (events.data ->> 'temperature_celsius')::double precision),
55              ('moisture_percent', (events.data ->> 'moisture_percent')::double precision),
56              ('conductivity', (events.data ->> 'conductivity')::double precision),
57              ('salinity', (events.data ->> 'salinity')::double precision),
58              ('tds', (events.data ->> 'tds')::double precision)
59      ) AS soil_metric(metric_name, metric_value)
60      WHERE events.type = 'sensors.soil.v1'
61  
62      UNION ALL
63  
64      SELECT
65          0 AS instance_index,
66          'solar_radiation_watts_per_square_meter'::text AS metric_name,
67          (events.data ->> 'solar_radiation_watts_per_square_meter')::double precision AS metric_value
68      WHERE events.type = 'sensors.solar_radiation.v1'
69  
70      UNION ALL
71  
72      SELECT
73          0 AS instance_index,
74          status_metric.metric_name,
75          status_metric.metric_value
76      FROM (
77          VALUES
78              ('memory_heap', (events.data ->> 'memory_heap')::double precision),
79              ('chip_cores', (events.data ->> 'chip_cores')::double precision),
80              ('chip_revision', (events.data ->> 'chip_revision')::double precision),
81              ('wifi_rssi', (events.data ->> 'wifi_rssi')::double precision),
82              ('uptime_seconds', (events.data ->> 'uptime_seconds')::double precision)
83      ) AS status_metric(metric_name, metric_value)
84      WHERE events.type = 'status.v1'
85  ) AS metric_extraction(instance_index, metric_name, metric_value)
86  JOIN metrics
87      ON metrics.type = events.type
88     AND metrics.name = metric_extraction.metric_name;