Re: [sqlite] json question

On Tue, Mar 27, 2018 at 8:22 PM, Ian Zimmerman wrote: > On 2018-03-27 19:26, Keith Medcalf wrote: > > > Actually, it is very simple and straightforward to implement > > hierarchical structures using Relational Database Models. > > But not recursive structures (or to be more precise, recursive que

Re: [sqlite] json question

> On Mar 28, 2018, at 5:34 AM, rene wrote: > > But people here have decided to use json. And I'm stuck with it. > > I was only trying to get faster then the already used C++ parser. One way > was to use sqlite's json parser. That's not going to help. Inserting the JSON into a SQL database, an

Re: [sqlite] json question

On 2018/03/28 2:34 PM, rene wrote: And I would love to use it. I'm not inclined to use json. But people here have decided to use json. And I'm stuck with it. Ah, a shotgun-marriage to a wrongful ideal.  Are you working at Micro$oft by any chance? :) I was only trying to get faster then the

Re: [sqlite] json question

Hello Warren, >author="Warren Young" >On Mar 28, 2018, at 5:51 AM, rene 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 inser

Re: [sqlite] json question

On Mar 28, 2018, at 5:51 AM, rene 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 dat

Re: [sqlite] json question

Hello, >author="Warren Young" >On Mar 28, 2018, at 3:28 AM, rene wrote: >> >> My main goal is currently to reduce the startup time. > >That’s not what I understood from your first post. Are you moving the goalposts, or is indexed read of >stored data not actually the primary problem

Re: [sqlite] json question

On Mar 28, 2018, at 3:28 AM, rene wrote: > > My main goal is currently to reduce the startup time. That’s not what I understood from your first post. Are you moving the goalposts, or is indexed read of stored data not actually the primary problem? > 1. parsing time is nearly the same between

Re: [sqlite] json question

Hello, Thank you for the suggestions so far. My main goal is currently to reduce the startup time. I have done some tests and compared the results to a pure C++ parser (nlohmann::json). 1. parsing time is nearly the same between "nlohmann::json::parse(..)" and "insert into data values(json(..))"

Re: [sqlite] json question

qlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Ian Zimmerman >Sent: Tuesday, 27 March, 2018 21:22 >To: sqlite-users@mailinglists.sqlite.org >Subject: Re: [sqlite] json question > >On 2018-03-27 19:26, Keith Medcalf wrote: > >> Actually, it

Re: [sqlite] json question

On 2018-03-27 19:26, Keith Medcalf wrote: > Actually, it is very simple and straightforward to implement > hierarchical structures using Relational Database Models. But not recursive structures (or to be more precise, recursive queries) which are the next very natural step. You can have a "paren

Re: [sqlite] json question

On Mar 27, 2018, at 7:26 PM, Keith Medcalf wrote: > >> The point of JSON support in SQLite, IMHO, is that it allows for >> hierarchical data structures, not something that is easy to do in a >> relational table-based DBMS like SQLite otherwise. > > Actually, it is very simple and straightforward

Re: [sqlite] json question

>The point of JSON support in SQLite, IMHO, is that it allows for >hierarchical data structures, not something that is easy to do in a >relational table-based DBMS like SQLite otherwise. Actually, it is very simple and straightforward to implement hierarchical structures using Relational Databas

Re: [sqlite] json question

On Mar 27, 2018, at 12:46 AM, rene wrote: > > "arrayname" : [ > { >"name" : "unique_name", >"id" : 1, >... > }, > .. > ] That looks like CREATE TABLE arrayname ( Id INTEGER NOT NULL PRIMARY KEY, name TEXT NOT NULL, etc INTEGER... ); to me. The point

Re: [sqlite] json question

> On Mar 26, 2018, at 11:46 PM, rene wrote: > > Question 1: Is it possible to create an index on "name" and "id" inside > sqlite Not really, because SQLite indexes only support values with a 1::1 relationship to table rows; in other words, the index can have only one entry per table row. But

[sqlite] json question

Hello, I have a big json string in an sqlite memory database. The json string contains also arrays like: "arrayname" : [ { "name" : "unique_name", "id" : 1, ... }, .. ] Currently I create my own index on "name" and "id" and get the values with: json_extract(db.json,'$.arraynam