5 ms·

Wait, what? Isn't storing JSON data as text in a relational DB against all kinds of normalisation rules? Under what circumstances should one do this?

by Lxr 10y ago

singingfish 10y ago
when you have arbitrary semi-structured data of limited scope to store. For example the stuff I was working on today is pluggable payment infrastructure. The vendor response is stored json (comes down the wire as xml or json, json is easier, I refuse to anticipate the structure of the data for future providers but I want the whole data returned for debug purposes), and the extra data it requires for transaction resolution is also json. Again I have no idea what this will look like for future payment providers, and this data will not result in consequences for other bits of the database.
- mrcarrot 10y ago
  Yep, I've done something very similar for payment processing (from multiple providers) recently. Have a well defined schema containing the columns that I _know_ I need now for handling a transaction, but also include a jsonb column that stores all of the data that the payment provider provides for the transaction. For one, this makes debugging easier, and it also means that should business needs change in the future and some field that we've been receiving becomes important for payment processing, it can be extracted from the json field and promoted to a column in the table, without having to _now_ define a load of columns for every possible field that every payment provider can ever supply.
- k__ 10y ago
  Simple example, settings. Stuff you want to make configurable at runtime.
- orf 10y ago
  > as text That's the issue he was referencing, I think.
- lsaferite 10y ago
  Another example from my space is product catalog data. There is large amount of data that is common and those are normalized properly. There is also a large amount of per product type variability that needs to be captured. One way to capture that is using JSON to store that specific data. Being able to index and query using the JSON structured data is a huge boon there.

Wait, what? Isn't storing JSON data as text in a relational DB against all kinds of normalisation rules? Under what circumstances should one do this?

by Lxr 10y ago

Wait, what? Isn't storing JSON data as text in a relational DB against all kinds of normalisation rules? Under what circumstances should one do this?

singingfish 10y ago

when you have arbitrary semi-structured data of limited scope to store. For example the stuff I was working on today is pluggable payment infrastructure. The vendor response is stored json (comes down the wire as xml or json, json is easier, I refuse to anticipate the structure of the data for future providers but I want the whole data returned for debug purposes), and the extra data it requires for transaction resolution is also json. Again I have no idea what this will look like for future payment providers, and this data will not result in consequences for other bits of the database.

mrcarrot 10y ago
Yep, I've done something very similar for payment processing (from multiple providers) recently. Have a well defined schema containing the columns that I _know_ I need now for handling a transaction, but also include a jsonb column that stores all of the data that the payment provider provides for the transaction. For one, this makes debugging easier, and it also means that should business needs change in the future and some field that we've been receiving becomes important for payment processing, it can be extracted from the json field and promoted to a column in the table, without having to _now_ define a load of columns for every possible field that every payment provider can ever supply.
k__ 10y ago
Simple example, settings. Stuff you want to make configurable at runtime.
orf 10y ago
> as text That's the issue he was referencing, I think.
lsaferite 10y ago
Another example from my space is product catalog data. There is large amount of data that is common and those are normalized properly. There is also a large amount of per product type variability that needs to be captured. One way to capture that is using JSON to store that specific data. Being able to index and query using the JSON structured data is a huge boon there.