Re:JSONB Has Landed in SQLite
by zmmmmm on Wednesday December 06, 2023 @05:30AM
(#38542271)
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.
[ Parent
| on HN ]
You can do this with text JSON in SQLite as well, but JSONB could speed it up.
What would that SQL look like in practice ?
Here'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;