4 ms·

What I have found with any relational database that has this kind of JSON storage option(MySQL, PostgresSQL) is that you can take some kind of data that arrives

What I have found with any relational database that has this kind of JSON storage option(MySQL, PostgresSQL) is that you can take some kind of data that arrives via 3rd party as JSON and just dump it straight into the db as JSON as opposed to having to create a schema for it. It's nice to have the option for the data to still be queryable without having to make it a first class schema with all schema setup involved. It isn't for 1st class data that is used frequently. But when you might only need the data on occassion its nice to have productivity-wise.

remus 8y ago
One pattern that I've used when parsing large json responses is to have a table schema like frequently|used|data|in|these|cols|full_json_response So you pull out the fields you need frequently but have the full json so you can dig in to details the response on an adhoc basis. Not very efficient as you can end up storing a big blob of json, but you're not throwing away any data when you parse the response and your schema stays very manageable. Also gives you the flexibility to add new fields to the schema by pulling the data out of the json.
- TremendousJudge 8y ago
  Completely agree, I do this all the time. This is a useful pattern.
- oefrha 8y ago
  I use this pattern a lot, too. Just make sure not to `SELECT *`, which could be much slower and waste a lot of RAM in some cases (ORMs using e.g. active record pattern are often susceptible to this when used naively).
- tracker1 8y ago
  I've also done similar where there are differing systems where the extra data is different, but common bits are, well common. For example supporting direct CC, Paypal and Amazon pay. Putting the common parts of the transaction as separate columns, with the full details in a JSON column.
acdha 8y ago
It's nice for being stage 1 of an import: pull the JSON in and use queries to populate other columns/tables with the extracted portions you're working with since you have the full power of a SQL database for many common cleanup tasks.

remus 8y ago

One pattern that I've used when parsing large json responses is to have a table schema like frequently|used|data|in|these|cols|full_json_response So you pull out the fields you need frequently but have the full json so you can dig in to details the response on an adhoc basis. Not very efficient as you can end up storing a big blob of json, but you're not throwing away any data when you parse the response and your schema stays very manageable. Also gives you the flexibility to add new fields to the schema by pulling the data out of the json.

TremendousJudge 8y ago
Completely agree, I do this all the time. This is a useful pattern.
oefrha 8y ago
I use this pattern a lot, too. Just make sure not to `SELECT *`, which could be much slower and waste a lot of RAM in some cases (ORMs using e.g. active record pattern are often susceptible to this when used naively).
tracker1 8y ago
I've also done similar where there are differing systems where the extra data is different, but common bits are, well common. For example supporting direct CC, Paypal and Amazon pay. Putting the common parts of the transaction as separate columns, with the full details in a JSON column.

acdha 8y ago

It's nice for being stage 1 of an import: pull the JSON in and use queries to populate other columns/tables with the extracted portions you're working with since you have the full power of a SQL database for many common cleanup tasks.