On Sun, Jul 7, 2013 at 8:40 PM, Roger Binns <rog...@rogerbinns.com> wrote: > On 07/07/13 16:19, Simon Slavin wrote: > > <http://unqlite.org> > What has generally become important to me is being able to supply > arbitrary JSON as a "record", be able to query it, and get the same > arbitrary JSON back out. > > In addition to unqlite, some of the other "nosql" databases have the same > approach (eg MongoDB). Even postgres is adding JSON storage via hstore. > > What hasn't standardised is how you query which means you can't easily > change engines or write (mostly) portable code like you can with SQL.
In this vein I'd like to point out my favorite JSON tool: jq (https://stedolan.github.io/jq) (author cc'ed). It has a very small, simple, and expressive language with much of the power of, say, XPath and XSLT, but none of their XML-ness, and much of the power of SQL as well. (Note an interesting similarity between SQLite and jq: both compile statements (SQLite) or expressions (jq) to byte code interpreted by a small VM.) > MongoDB uses JSON shaped structures with $prefixed operators. unqlite is > using a custom Jx9 interpreted language. And postgres uses minor > extensions to SQL. There was unql which DRH was involved in two years > ago, but appears unadopted. I'm imagining a combination of SQL and jq. In particular, I've wanted a SQL extension where "paths" can be used to concisely express JOINs. For example: CREATE TABLE foo (foo_id, name, some_stuff, other_stuff); CREATE TABLE bar (bar_id, foo_id REFERENCES foo (id), bar_stuff); CREATE TABLE baz (baz_id, bar_id REFERENCES bar (id), baz_stuff); SELECT baz.bar_id.foo_id.name, baz.bar_id.foo_id.some_stuff FROM baz WHERE baz_stuff = :argument; being equivalent to: SELECT foo.name, foo.some_stuff FROM baz LEFT JOIN bar ON baz.bar_id = bar.bar_id LEFT JOIN foo ON bar.bar_id = foo.foo_id WHERE baz.baz_stuff = :argument; This is nice in that it's natural to take a portion of a path that would refer to a JSON document and... follow it there. This would allow one to write queries with much less knowledge of the schema and the boundary between relational data and document data. It should also be possible to express paths from table foo to bar and baz (in this example). I'm not sure what the syntax for those should look like, maybe: foo<-bar.foo_id<-baz.bar_id->baz_stuff. Also, it might be possible to express index key contents (and other data to be covered by an index) using path expressions as well. Combine the power of SQL and something like jq and we're really talking about serious expressive power. Note that if query results are expressed in JSON then some parts of a query could be evaluated on a client/app side, and some on the server/SQL engine side, without the app having to be aware of it. The idea is to make the boundary between relational data and document seamless. > My favourite is the MongoDB approach since the queries and data are > substantially similar - it is very similar to Query By Example. QBE is nice and simple, but lacks expressive power. It's useful to start there, but not paint oneself into a syntactic corner where it becomes difficult to add expressive power in a user-friendly way. Nico -- _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
