3 ms·
as 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
by mlevental 7y ago
as 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)).)