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 Mertens <wout.mert...@gmail.com> wrote: > 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, <colei...@gmail.com> 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
