4 ms·
What would that SQL look like in practice ?
by euroderf 3y ago
What would that SQL look like in practice ?
- ako 3y agoHere's an example i have running on a pi with a temperature sensor. All data is received as json messages in mqtt, then stored as is in a postgres table. The view turns it into relational data. If i just need the messages between certain temperatures i can speed this up by adding an index on the 'temperature' field in json. create or replace view bme680_v(ts, message, temperature, humidity, pressure, gas, iaq) as SELECT mqtt_raw.created_at AS ts , mqtt_raw.message , (mqtt_raw.message::jsonb ->> 'temperature'::text)::numeric AS temperature , (mqtt_raw.message::jsonb ->> 'humidity'::text)::numeric AS humidity , (mqtt_raw.message::jsonb ->> 'pressure'::text)::numeric AS pressure , (mqtt_raw.message::jsonb ->> 'gas_resistance'::text)::numeric AS gas , (mqtt_raw.message::jsonb ->> 'IAQ'::text)::numeric AS iaq FROM mqtt_raw WHERE mqtt_raw.topic::text = 'pi/bme680'::text ORDER BY mqtt_raw.created_at DESC;