Thank you. What about trying to make use of the JSON1 extension's table-valued functions?
Like this, for example. It worked pretty well, as far as I can tell (the data is written and accurate); but I'm not a programmer and have nothing to which to compare it. I'm sure that's obvious from the code and question alone.
Does the optimization note under section 3.1 of the With Clause apply at all when it is not recursive? I mean are the first two tables completely built in memory before anything is inserted, or does SQLite process one row at a time from the table storing the existing data, inserting all of the resultant rows from it into the empty table, and then moves to the next row?
set sql {
with \
iters as ( \
select version as t, book_no as b, chapter_no as c, verse_no as v \
from Bible_translations \
where t=0 ),\
jeach as ( \
select it.t as t, it.b as b, it.c as c, it.v as v, \
json_extract( jt.value, '$.j' ) as j, \
json_extract( jt.value, '$.w' ) as w, \
json_extract( jt.value, '$.p' ) as p \
from iters it, json_each( (select transfunc(version, book_no, chapter_no, verse_no, text_content) \
from Bible_translations bt \
where bt.version=it.t \
and bt.book_no=it.b \
and bt.chapter_no=it.c \
and bt.verse_no=it.v)) jt) \
insert into concordance (version, book_no, chapter_no, verse_no, index_no, word_orig, word_clean) \
select * from jeach;}
dbt function transfunc -argcount 5 -deterministic -directonly -returntype text {jsonbuild}
proc jsonbuild { t b c v w } {
global punc
set u [encoding convertfrom utf-8 $w]
set json [join lmap word [split $u] {
incr j
set p [string map $punc $word]
set word "\"p[incr a]\":{\"t\":$t,\"b\":$b,\"c\":$c,\"v\":$v,\"j\":$j,\"w\":\"$word\",\"p\":\"$p\"}"
}] ,]
return \{$json\}
}