JSONB has landed

Post by ako » Wed, Dec 06, 2023, 2:11 PM UTC

euroderf wrote: post #95

zmmmmm wrote: post #90

Earlier quoted context omitted.

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.

What would that SQL look like in practice ?

    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;

Post by jchanimal » Wed, Dec 06, 2023, 2:47 PM UTC

ako wrote: post #121

mike_d wrote: post #38

Earlier quoted context omitted.

JSON is unordered. Nothing in your code should assume otherwise. "An object is an unordered collection of zero or more name/value pairs, where a name is a string and a value is a string, number, boolean, null, object, or array."

Assume you're writing an editor for JSON files. Don't think many users of that editor would be very happy if you change the order of the attributes in their json files, even though technically it's the same...

Post by rav » Wed, Dec 06, 2023, 3:03 PM UTC

piaste wrote: post #97

buzziebee wrote: post #71

Earlier quoted context omitted.

Yeah migrations are the biggest issue for me. I really don't like not knowing what the actual shape of the document will be. Missing transactions, and not great relationship performance makes modelling some systems more hassle than it's worth. I gave it a good go to use mongo and firestore for a few projects, but after a year or two of experimenting I'll be sticking to SQL based DBs unless there are super clear and o…

There's a gradual approach there, where you start out with a JSONB column, and then as each piece of the data structure stabilizes* you move it out of json fields and into its own columns/tables. * meaning, when there's enough code that depends on it that changing it would require some planning

Post by golergka » Wed, Dec 06, 2023, 3:21 PM UTC

ako wrote: post #121

mike_d wrote: post #38

Earlier quoted context omitted.

JSON is unordered. Nothing in your code should assume otherwise. "An object is an unordered collection of zero or more name/value pairs, where a name is a string and a value is a string, number, boolean, null, object, or array."

Assume you're writing an editor for JSON files. Don't think many users of that editor would be very happy if you change the order of the attributes in their json files, even though technically it's the same...

Post by piaste » Wed, Dec 06, 2023, 3:34 PM UTC

rav wrote: post #126

piaste wrote: post #97

Earlier quoted context omitted.

There's a gradual approach there, where you start out with a JSONB column, and then as each piece of the data structure stabilizes* you move it out of json fields and into its own columns/tables. * meaning, when there's enough code that depends on it that changing it would require some planning

Alternatively, keep it in the JSON/JSONB column until you need to search/filter/query on it, in which case you pull it out into a column.