[xquery-talk] the stupidity of using SQL as a query language for JSON

Michael Kay mike at saxonica.com
Mon Jun 1 14:28:44 PDT 2015


> 
> 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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://x-query.com/pipermail/talk/attachments/20150601/26124180/attachment.html>


More information about the talk mailing list