> On Nov 9, 2016, at 3:04 AM, Wout Mertens <wout.mert...@gmail.com> wrote: > >> Indexes (even expression indexes) require a 'real' table. >> You'd have to put the results of json_each() into a separate table that >> can then be indexed. > > I assume I can use some sort of trigger setup to automatically update that > derived table when a row gets inserted, deleted or updated? Any pointers on > how to do that?
I’ve done similar things, in implementing map/reduce on top of SQLite, but I don’t think the approach I used will help you because it relied on some specifics of the schema I was using, and it also wasn’t as fast as it could have been. I need to revisit it soon, as I’m rewriting that same code. The trigger approach looks promising. One complication, that doesn’t show up in that snippet on the FTS5 page, is that a row in the source table can produce multiple rows in the derived table. So the derived table would need a foreign key referencing the rowid in the source table, so that when a source row is updated or deleted all the derived rows can be replaced. (If you get this working, I’d be interested to see the SQL code for it.) —Jens _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
