3 ms·
SQLite has really good JSON support these days. Any reason not to use that?
by simonw 7y ago
SQLite has really good JSON support these days. Any reason not to use that?
- mlevental 7y agoas far as i could tell you have to jump through some hoops to use it (compile extension from source?). and i couldn't tell if it supported querying
- lacampbell 7y agoJSON on Sqlite works out of the box in ubuntu, but it doesn't in OpenBSD. Not sure about other environments. It supports querying, see json_extract function. https://www.sqlite.org/json1.html https://www.sqlite.org/json1.html I use it a lot to return JSON from normalized data, for apps to consume.
- bruce_one 7y agoThe extension is included in the standard sqlite [amalgamation](https://www.sqlite.org/amalgamation.html https://www.sqlite.org/amalgamation.html), hence it's basically always available (ie you'd have to manually disable it) (ime). It's worth noting that sqlite's typing is very flexible, so you probably want to add a `check (data is null or json_valid(data) = 1)` to ensure the data is _actually_ valid json. You can then index based on json queries, eg `create index some_index on something((json_extract(data, '$.someField')))` and then do an indexed query, eg `select * from something where json_extract(data, '$.someField') = 'blah'`. In our experience it's all working quite well :-) (Groking json_each and json_tree took a few goes, but now is working well (eg we join onto json_each to allow a row "labels" containing a list of label (rather than a one-to-many table join for something_labels)).)