7 ms·
I don't know if it's true of SQLite, but you missed the most important point at least with PostgreSQL : you can build indexes directly against attributes inside
by zmmmmm 3y ago
I don't know if it's true of SQLite, but you missed the most important point at least with PostgreSQL : you can build indexes directly against attributes inside JSONB which really turns it into a true NoSQL / relational hybrid that can let you have your cake and eat it too for some design problems that don't fit neatly into either pure relational or pure NoSQL approaches.
- euroderf 3y agoWhat 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;
- debugnik 3y agoYou can do this with text JSON in SQLite as well, but JSONB could speed it up.