> I'd rather have more speed
Maybe this wasn't clear. The query I showed has 20 *leaf* `json_extract` calls.
It has 3 *nested* `json_each` joins, for traversal of the JSON hierarchy,
to reach the each individual fields of all entities:
```
select ...,
json_extract(field.value, '$.DESC'),
...
from pg,
json_each(pg.js, '$') dict,
json_each(dict.value, '$.entities') entt,
json_each(entt.value, '$.fields') field
```
I didn't exactly follow what's going on in the debugger, but here's my understanding of what's going on in this query:
* SCAN the pg table.
* for each row (I have 4 of them), parse js column, which is in JSON format.
* the first `json_each(, '$')` fully parses that JSON value,
and produces a text value for the top-level objects
(I just realize this one might not be necessary :))
At this point, we've parsed the JSON 1x I believe,
unless there's an optimization for `'$'` which might be the *identity*.
* the next `json_each(, '$.entities')` parses `dict.value`, and produces a dictinct value for all values of the *entities* array.
So we've reparsed basically the document almost entirely another time (2x).
* then the same happens for the *fields* array inside each entity.
We're reparsing most of the JSON another time (3x).
* and finally we extract of all values of interest from the *leaf* objects;
using `json_extract`. I suspect each extract is a separate partial parse of that object,
until it finds the key of interest. Assuming 1st key needs only parsing 1/20th of the object,
last (20th) key needs to parse 100% of the object.
So 1/20 + 2/20 + ... + 20/20 = 20*21/2/20 = 10.5 times the JSON value.
So if my count is correct, that query parses 13.5 time each top-level (4) JSON doc.
That's what I mean when I wish for more speed. That only a single parse was necessary.
So unless SQLite added the possibility to return views into input strings (or blobs)
when it can guarantee the lifetime of the value it returns a view from
(like in the case above I suspect, since SQLite only supports nested loops, and the loop-order is fixed in the query above),
as good/fast the SQLite JSON parser is, doing the parsing an order of magnitude too many times is not ideal.
That's why I'd hope for changes in SQLite that would allow JSON to be faster in SQLite.
I'd prefer JSON support to stay in the JSON1 extension, until the above behavior can be avoided.
That's assuming my analysis is correct. I'd be happy to be told I'm not looking at this correctly.