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

The extension is included in the standard sqlite [amalgamation](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)).)

JSON 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

I use it a lot to return JSON from normalized data, for apps to consume.