Help with json1 query?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Help with json1 query?

Charles Leifer
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Help with json1 query?

wmertens
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, <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Help with json1 query?

Charles Leifer
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 <[hidden email]>
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, <[hidden email]> 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
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users