Example using the json1 extension:<p>BEGIN TRANSACTION;<p>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;<p>CREATE INDEX json_tbl_idx on json_tbl(json_extract(json, '$.value'));<p>EXPLAIN QUERY PLAN SELECT * FROM json_tbl_view WHERE 'the_value_33' = val;<p>COMMIT;
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.<p>It makes inserts and updates more expensive, because the calculated values must be recalculated, but it can be an awesome win.<p>Hooray SQLite!
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".<p>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.<p>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.
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.