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
