CREATE TABLE json_tbl(id integer primary key, json text collate nocase);
CREATE VIEW json_tbl_view AS
SELECT id, json_extract(json, '$.value') AS val
FROM json_tbl;
CREATE INDEX json_tbl_idx on json_tbl(json_extract(json, '$.value'));
EXPLAIN QUERY PLAN SELECT * FROM json_tbl_view WHERE 'the_value_33' = val;
If I understand correctly and this is similar to Postgres' functionality, this is really awesome for any calculated value (particularly anything expensive) that you frequently need to search on.
It makes inserts and updates more expensive, because the calculated values must be recalculated, but it can be an awesome win.
SQLite gets better and better all the time. Considering that hard disks and memories get faster and cheaper as well SQLite is even becoming a serious option for handling "big data".
I use it in several of my personal and professional projects, especially in cases where data can be sharded well. The one thing that is a bit annoying though is the locking mechanism in SQLite, which prevents simultaneous writes to the database and locks it for reading while a write transaction is underway. If they could find a way to improve that behavior I think the use cases of SQLite would grow way beyond embedded application databases. Whether or not this is a good thing is of course a different question, but having a zero-configuration "plug-and-play" database engine could be a very attractive proposition to many people.
BerkeleyDB for example, which handles concurrent access much better than SQLite and also comes in a high-availability version is used in many production environments today, most notably as the backend of Amazon's DynamoDB key/value store.
If SQLite can handle your data handling needs, then almost by definition you are not doing "big data".
Quoting my friend Wikipedia:
"Big data is a broad term for data sets so large or complex that traditional data processing applications are inadequate."
But yes, I agree that SQLite gets better all the time.
Last time I check, multi-process byte-range file locking was a mess. I'm surprised by your statement about BerkeleyDB, because as far as I can tell it simply cannot be safely implemented.
SQLite addressed writers blocking readers in 3.7.0 with its Write-Ahead Log. See https://www.sqlite.org/wal.html for more details, but point 2 at the top is "WAL provides more concurrency as readers do not block writers and a writer does not block readers. Reading and writing can proceed concurrently."
Berkeleydb comes with a Sqlite api-compatible library. Using this, you effectively get sqlite with page level locking as opposed to database. Instructions here for compiling:
I really wish things like blog and forum software would use sqlite more often. It makes installation and backup trivial while being good enough for the vast majority of cases.
BEGIN TRANSACTION;
CREATE TABLE json_tbl(id integer primary key, json text collate nocase); CREATE VIEW json_tbl_view AS SELECT id, json_extract(json, '$.value') AS val FROM json_tbl;
CREATE INDEX json_tbl_idx on json_tbl(json_extract(json, '$.value'));
EXPLAIN QUERY PLAN SELECT * FROM json_tbl_view WHERE 'the_value_33' = val;
COMMIT;