Hacker News Reader

Comment by Avshalom | Hacker News Reader

An sqlite db file is probably as light as your json or csv AND better formed/typed

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).

Can but probably won't.

You 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.

Not an SQL expert but pretty sure two tables joined together is extremely normal and well represented in RDBMS

Yep, 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.

Speaking of which, I've not grokked jq's functional approach to composing queries. Can anyone suggest a good approach to understanding jq please?

Two 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.

Your version is denormalized. It is faster, but also more likely to have misspellings and duplicates. It really depends what is more important.

The JSON example that it was compared to was also denormalized.

Exactly. You could put it in one table if you really wanted to (not that it would be ideal by any means).

I'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.

Also the JSON1 extension.

https://www.sqlite.org/json1.html

Most 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).

`imbrications`: overlapping, as in roof tiles

(thought it was a typo, now I’m not sure)

Reply on news.ycombinator.com