5 ms·
It’s easier to compare it to CSV than JSON, because the former is tabular while the latter can have deep imbrications that aren’t well represented in SQL(ite).
by hk__2 9y ago
It’s easier to compare it to CSV than JSON, because the former is tabular while the latter can have deep imbrications that aren’t well represented in SQL(ite).
- Avshalom 9y agoCan but probably won't.
- hk__2 9y agoYou don’t need to go deep: `{"name": "Bob", "hobbies": ["soccer", "cinema", "music"]}`. That’s 3 tables in SQL(ite): one for the people; one for the hobbies; and one to join both.
- Avshalom 9y agoNot an SQL expert but pretty sure two tables joined together is extremely normal and well represented in RDBMS
- voltagex_ 9y agoYep, I can write an INNER JOIN faster than I can remember jq's arcane syntax. If you needed it all in one flat table for some reason, SQLite supports views.
- wongarsu 9y agoTwo tables: Person: {id, name} and Hobbies: {person_id, name}. Then you can "SELECT person.name, array_agg(hobbies.name) FROM person JOIN hobbies ON (person.id = hobbies.person_id)" to get your json representation back (at least with postgresql, "array_agg" isn't in standard SQL) That's a pretty simple join, any real database layout I've seen goes much more complicated and much deeper than that.
- takeda 9y agoYour version is denormalized. It is faster, but also more likely to have misspellings and duplicates. It really depends what is more important.
- dotancohen 9y agoThe JSON example that it was compared to was also denormalized.
- mythrwy 9y agoExactly. You could put it in one table if you really wanted to (not that it would be ideal by any means).
- zackelan 9y agoI'd argue that the more large/complicated/nested the JSON structure, the more it would benefit from using a "real" database instead of a JSON file sitting on disk. If not SQLite due to document-relational mismatch, then LevelDB, RocksDB, or one of the other embedded key-value stores. Of course, SQLite can also emulate a key-value store quite well, with a table with 'key' and 'value' columns.
- mythrwy 9y agoAlso the JSON1 extension. https://www.sqlite.org/json1.html https://www.sqlite.org/json1.html
- wongarsu 9y agoMost SQL databases are much more like JSON than CSV: deeply nested and interconnected. It's not unusual to join three different tables together to get something you want, which is effectively the same as nested JSON (though a bit more flexible, because it allows for circular dependencies and the like).
- chrisweekly 9y ago`imbrications`: overlapping, as in roof tiles (thought it was a typo, now I’m not sure)