Re: [sqlite] Help with json1 query?
No, the keys would be arbitrarily chosen by the user. The rtree extension could be a possibility, I'll check it out. On Thu, Mar 15, 2018 at 12:56 AM, Wout Mertenswrote: > Can you elaborate on the metadata? Are the keys always the same, in which > case you could store them as columns? > > There's also the https://sqlite.org/rtree.html extension which lets you > efficiently query multidimensional range data. > > If there is truly no schema, what you propose is the only way AFAIK. > > On Wed, Feb 28, 2018, 10:52 PM Charles Leifer, wrote: > > > Hi, > > > > I'm prototyping a little graph library using SQLite. My idea is to store > > vertices in a simple table like this: > > > > CREATE TABLE "vertex" ("key" TEXT NOT NULL PRIMARY KEY, "metadata" JSON); > > CREATE TABLE "edge" ( > > "id" INTEGER NOT NULL PRIMARY KEY, > > "src" TEXT NOT NULL, > > "dest" TEXT NOT NULL, > > "metadata" JSON, > > FOREIGN KEY ("src") REFERENCES "vertex" ("key"), > > FOREIGN KEY ("dest") REFERENCES "vertex" ("key")); > > > > What I'd like to do is allow querying of edges (or vertices) using a > > *partial* metadata object. So if I had the following JSON object stored > in > > an edge's metadata: > > > > {"k1": "v1", "k2": "v2", "k3": "v3"} > > > > The user could provide me an object like {"k1": "v1", "k3": "v3"} and I > > would be able to match the above edge's metadata. > > > > I can see decomposing the user-provided dictionary and building up > multiple > > equality tests using the json_extract() function, e.g.: > > > > select * from edge where json_extract(metadata, '$.k1') = 'v1' AND > > json_extract(metadata, '$.k3') = 'v3'; > > > > But I was hoping there would be a more elegant way to express this that > > someone would be able to share? It seems as though I should be able to > use > > `json_each()` (or even `json_tree()` if metadata could be nested?), but > I'm > > not sure how to formulate the query. > > > > It'd be great if there were a JSON function like "json_contains()" where > I > > could write: > > > > select * from edge where json_contains(metadata, '$', '{"k1": "v1", "k3": > > "v3"}'); > > > > Any help appreciated! > > > > Charlie > > ___ > > sqlite-users mailing list > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help with json1 query?
Can you elaborate on the metadata? Are the keys always the same, in which case you could store them as columns? There's also the https://sqlite.org/rtree.html extension which lets you efficiently query multidimensional range data. If there is truly no schema, what you propose is the only way AFAIK. On Wed, Feb 28, 2018, 10:52 PM Charles Leifer,wrote: > Hi, > > I'm prototyping a little graph library using SQLite. My idea is to store > vertices in a simple table like this: > > CREATE TABLE "vertex" ("key" TEXT NOT NULL PRIMARY KEY, "metadata" JSON); > CREATE TABLE "edge" ( > "id" INTEGER NOT NULL PRIMARY KEY, > "src" TEXT NOT NULL, > "dest" TEXT NOT NULL, > "metadata" JSON, > FOREIGN KEY ("src") REFERENCES "vertex" ("key"), > FOREIGN KEY ("dest") REFERENCES "vertex" ("key")); > > What I'd like to do is allow querying of edges (or vertices) using a > *partial* metadata object. So if I had the following JSON object stored in > an edge's metadata: > > {"k1": "v1", "k2": "v2", "k3": "v3"} > > The user could provide me an object like {"k1": "v1", "k3": "v3"} and I > would be able to match the above edge's metadata. > > I can see decomposing the user-provided dictionary and building up multiple > equality tests using the json_extract() function, e.g.: > > select * from edge where json_extract(metadata, '$.k1') = 'v1' AND > json_extract(metadata, '$.k3') = 'v3'; > > But I was hoping there would be a more elegant way to express this that > someone would be able to share? It seems as though I should be able to use > `json_each()` (or even `json_tree()` if metadata could be nested?), but I'm > not sure how to formulate the query. > > It'd be great if there were a JSON function like "json_contains()" where I > could write: > > select * from edge where json_contains(metadata, '$', '{"k1": "v1", "k3": > "v3"}'); > > Any help appreciated! > > Charlie > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Help with json1 query?
Hi, I'm prototyping a little graph library using SQLite. My idea is to store vertices in a simple table like this: CREATE TABLE "vertex" ("key" TEXT NOT NULL PRIMARY KEY, "metadata" JSON); CREATE TABLE "edge" ( "id" INTEGER NOT NULL PRIMARY KEY, "src" TEXT NOT NULL, "dest" TEXT NOT NULL, "metadata" JSON, FOREIGN KEY ("src") REFERENCES "vertex" ("key"), FOREIGN KEY ("dest") REFERENCES "vertex" ("key")); What I'd like to do is allow querying of edges (or vertices) using a *partial* metadata object. So if I had the following JSON object stored in an edge's metadata: {"k1": "v1", "k2": "v2", "k3": "v3"} The user could provide me an object like {"k1": "v1", "k3": "v3"} and I would be able to match the above edge's metadata. I can see decomposing the user-provided dictionary and building up multiple equality tests using the json_extract() function, e.g.: select * from edge where json_extract(metadata, '$.k1') = 'v1' AND json_extract(metadata, '$.k3') = 'v3'; But I was hoping there would be a more elegant way to express this that someone would be able to share? It seems as though I should be able to use `json_each()` (or even `json_tree()` if metadata could be nested?), but I'm not sure how to formulate the query. It'd be great if there were a JSON function like "json_contains()" where I could write: select * from edge where json_contains(metadata, '$', '{"k1": "v1", "k3": "v3"}'); Any help appreciated! Charlie ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users