On Tue, Mar 26, 2019 at 3:35 PM Wout Mertens <wout.mert...@gmail.com> wrote:
> Hi amazing list,
>
> what would be the best way to answer these, given `CREATE TABLE foo(id
> TEXT, json JSON);` and json is always a json object:
>
> - all rows with a given key bar
> - SELECT * FROM foo WHERE json_extract(json, '$.bar') IS NOT NULL;
> - all rows where there are only any of the given keys a,b in the object
> - SELECT * FROM foo WHERE json_remove(json,'$.a','$.b') ='{}';
> - all rows where there are all the given keys a,b and no others in the
> object
> - SELECT * FROM foo WHERE json_remove(json,'$.a','$.b') ='{}' and
> json_extract(json, '$.a') IS NOT NULL and json_extract(json,
> '$.b') IS NOT
> NULL;
>
> these queries seem pretty onerous to me, I hope there are better ways…
>
Maybe using https://www.sqlite.org/json1.html#jtype might be faster?
I think it's possible that when you use several json_ functions, notably in
your WHERE
clause, the document is reparsed several times. I haven't looked at the
detail for a while, but I think
internally JSON1 can work on an intermediate binary representation that's
more efficient,
but only when call-chaining (depends on subtype APIs I seem to remember),
which doesn't help here.
Unfortunately it doesn't work with ephemeral tables AFAIK, when using a CTE
for example, to force the parsing
once, and then process the different json_ function off that more efficient
representation. --DD
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
