On Mar 28, 2018, at 5:51 AM, rene <[email protected]> wrote:
>
>> I’m suggesting that you don’t use SQLite’s JSON features at all.
>
> Oh no. Either I use the C++ parser or I use sqlite.
I’m suggesting that you do both: Parse the JSON with C++, then insert it in
normalized SQL form into SQLite, then query the data with pure ANSI SQL-92.
If you absolutely need JSON querying at the SQLite level, you can split the
difference:
CREATE TABLE arrayname (
id INTEGER NOT NULL PRIMARY KEY,
name TEXT NOT NULL,
json TEXT NOT NULL DEFAULT ‘'
);
Parse the id and name column values from the JSON text, then insert the JSON
text and the parsed values into the table. Now you can key off off the id
and/or name, so you needn’t do a full table scan even when querying fields of a
specific JSON object, if you can address it by ID and/or name.
> The C++ parser has no knobs to tune.
Why do you need knobs? What specific benefit do you get from these knobs?
> With this option I have more options in selecting data.
That sounds like a distinction without a difference. The SQL query language is
very powerful as-is, without adding SQLite’s JSON querying features.
I’m not against SQLite’s JSON feature, for what it’s worth, I’m just not seeing
what it actually buys you.
If you think I’m wrong, show some example code giving a query you can do in
JSON that you cannot do efficiently in plain SQL. I suspect if you do this,
someone will quickly come along and show you how to get the same speed or
faster in plain old ANSI SQL.
Also show your SQL schema and a more representative sample of your JSON input
data. It may simply be that you have an understanding of both that I do not
because I’m left to guess. Getting concrete might help a lot.
> It would be really cool if the json_extract() function could support a
> wildcard as array index and return all values p.e.
>
> json_extract(data.json,'$.arrayname[*].name’)
If that really is the way you need to query your data, then I think you want
something made from the start to be a JSON DBMS, not this feature of SQLite’s.
> Oh, regarding json_extract(). If I do a "explain query .." on it, it always
> do a table scan.
I’d have been amazed if it had behaved any differently.
One would design SQLite differently if arbitrary JSON were a first-class data
type. Since the binary data format didn’t change when this feature was added,
we can infer that arbitrary JSON is not a first-class data type; they’re just
strings which have to be repeatedly re-parsed to get anything useful done.
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
