On Feb 12, 2020, at 10:53 AM, Jens Alfke <j...@mooseyard.com> wrote:
>
> You should be able to speed this up by creating temporary tables from the
> JSON first, and then changing the CTE to use those tables.
Do you not get the same effect by using the new generated columns feature, only
without the manual work of maintaining the temporary table?
https://www.sqlite.org/gencol.html
sqlite> create table a (
json text,
b text generated always as (json_extract(json, '$.field')) stored
);
sqlite> insert into a values('{"field": "hello"}');
sqlite> select b from a;
hello
It’s probably critical to the success of this that you use the STORED attribute
rather than VIRTUAL, which means you can’t ALTER TABLE your way to success, but
you’d be looking at table copies with the temporary table idea anyway.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
