Re: JSON_EACH + recursive query = unexpected performance degradation

On Feb 12, 2020, at 10:53 AM, Jens Alfke <[email protected]> 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
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users