> > So SQL over JSON = Turing Tarpit. Yep. Fair summary.
Dana P.S. BTW, in the complexity of the the simple query below, I forgot that every nested query has also a sortby. And the nested queries are not un-nestable :-))) I even wonder if such queries can be written in SQL-99…:-) > > Fair summarisation? > > On Mon, Jun 1, 2015 at 5:28 PM, Michael Kay <[email protected] > <mailto:[email protected]>> wrote: >> >> Well, if you’re going to apply SQL to JSON (say), then the first thing you >> have to do is define a mapping from JSON to tables. That’s not difficult to >> do. >> >> If by the “closure property” you want the result of any SQL query to be the >> representation of some JSON structure, then you’re not going to achieve >> that. That’s essentially the same as the update problem. >> >> >> So pretty much no subqueries (ok somebody is going to say that any query >> entailing a subquery can be rewritten without one) but AFAIC the language >> you are talking about isn't really SQL then. > > I don’t follow. You can write any query you like; it’s just that the result > is a table that might not be mappable back to JSON. > >>> >> >> So I'm SOL if it's a date then. > > You won’t get any dates in the table representation of JSON, but you can get > them in tables returned by a query. >> >>> >> >> Forgive me if I'm wrong. But I still don't believe I can write a statement >> that gets me the 5th row of the table. > > Well, a typical representation of a JSON array [“a”, “b”, “c”] might be the > table > > ARRAYS > ID INDEX TYPE VALUE > 001 0 String “a” > 001 1 String “b” > 001 2 String “c” > > and the query to get item 2 of array 001 would be > > SELECT TYPE, VALUE FROM ARRAYS WHERE ID=001 AND INDEX=2 > > As far as I understand it this is similar to the mapping that SQL Server uses > for XML. > >> >> >> How would you predict a priori what your table and column names would be >> and how many of them. > > If you’ve got no schema then you have to use a generic mapping in which the > table and column names are generic concepts such as ARRAY, MAP, etc. > > >> >> >> If you don't can't predict the schema your semi-structured is going to >> generate then how could you write your query. > > With difficulty. I can’t see anyone wanting to write it by hand. > > Michael Kay > Saxonica >
_______________________________________________ [email protected] http://x-query.com/mailman/listinfo/talk
