I’m no expert in JSON, but don’t you lose the ability to filter it before your application receives it all? If you had a reasonable amount of data then in SQL you can add WHERE clause and cut down what you get back so you could end up processing a lot less data than in your JSON example, even with the duplicated top table data. Plus if you’re sensible you can ensure you’re not bringing back more fields than you need.
I’m no expert in JSON, but don’t you lose the ability to filter it before your application receives it all? If you had a reasonable amount of data then in SQL you can add WHERE clause and cut down what you get back so you could end up processing a lot less data than in your JSON example, even with the duplicated top table data. Plus if you’re sensible you can ensure you’re not bringing back more fields than you need.
It’s entirely possible to sort and filter inside JSON data in most SQL dialects. You can even add indexes.
Postgres has JSON operators you can put into expressions, including WHERE clauses. You can write queries like:
SELECT item_details->‘manufacturer’->‘location’ FROM inventory WHERE item_details->‘material’ = “concrete”;
Storing data in JSON columns has other trade-offs, though.
In a traditional SQL database, yeah. In various document-oriented (NoSQL) databases, though, you can do that.
Modern relational databases have support for it too including indexes etc. For example postgres.
Every major SQL database supports json manipulation nowadays. I know MariaDB and MySQL and SQLite at least support it natively.