Yep, SQLite is awesome. I did actually build exactly that: a document database powered by SQLITE [1], and it works well, especially considering that SQLite has built-in support for full text search and indexes for JSON content.
In the past I tested importing dumps of the full HTML and CSS documentation docset from MDN and: - Importing files in a directory is relatively fast: I import batches and it can import hundreds of pages in a few seconds - sadly there's no bulk operation yet to insert arbitrary json documents (unless they are stored as files in a folder), so that's quite slow. - full text search and querying indexed (JSON) properties is fast (double-digit milliseconds for searching several hundred of relatively large documents with paged results)
I do need to do some compared benchmarks with similar data stores though.
How did you get sqlite3 to index JSON? I don't see anything in the product that does that. I'm searching through your source code now but I don't see anything specific.
Regarding indexing json... here's how I do it:
proc createIndex*(store: Datastore, indexId, field: string) =
let query = sql("CREATE INDEX json_index_$1 ON documents(json_extract(data, ?) COLLATE NOCASE) WHERE json_valid(data)" % [indexId])
store.begin()
store.db.exec(query, field)
store.commit()
I then added a REST API in LiteStore to create (JSON) indexes... in that way you can make your queries more performant based on the specific json documents that you store in the data store.