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

Reply via email to