Over 1 ½ years ago I suggested that SQL will turn out to be an important factor for the success of NoSQL databases (https://realprogrammer.wordpress.com/2014/03/04/sql-the-kingmaker-of-nosql-databases/). Did that happen?
NoSQL / OldSQL / NewSQL: SQL Language Support on the Rise
When surveying a subset of the various database systems it becomes apparent that the support for relational SQL as well as JSON-supporting SQL is increasing. Right now this does not necessarily mean that there is an agreement on the particular language syntax, its semantic interpretation or execution style (declarative vs. functional vs. procedural). However, there is an (implicit) agreement that a (declarative) query language is necessary or advantageous, that SQL is a guiding principle and that JSON needs to be supported to various extent. A (not necessarily complete) list of different systems follows that have a (JSON-supporting) SQL language implemented in one form or another:
- 28msec http://www.28.io/ (JSONiq)
- Aerospike http://www.aerospike.com/ (AQL)
- AsterixDB http://asterixdb.ics.uci.edu/ (AQL)
- Azure DocumentDB https://azure.microsoft.com/en-us/services/documentdb/
- Cassandra http://cassandra.apache.org/ (CQL)
- Couchbase http://www.couchbase.com/ (N1QL)
- FoundationDB http://www.infoq.com/news/2014/09/foundationdb-sql-layer
- IBM DB2 http://www-01.ibm.com/support/knowledgecenter/ SSEPGG_10.5.0/com.ibm.swg.im.dbclient.json.doc/doc/ c0061252.html?lang=en
- Informix http://www-01.ibm.com/support/knowledgecenter/ SSGU8G_12.1.0/com.ibm.jsonnode.doc/ jsonnode.htm
- MongoDB https://www.mongodb.org/ (BI-Connector)
- MySQL https://dev.mysql.com/doc/refman/5.7/en/json.html
- Oracle 12c https://docs.oracle.com/database/121/ADXDB/json.htm#ADXDB6246
- PostgreSQL http://www.postgresql.org/docs/9.4/static/datatype-json.html
- SQL Server https://msdn.microsoft.com/en-us/library/dn921897.aspx
- VoltDB https://voltdb.com/blog/querying-json-data-voltdb
From the above selection it becomes clear that a lot of NoSQL, OldSQL, and NewSQL systems invest in SQL as well as JSON-supporting SQL and that the number of systems that support (JSON-supporting) SQL is definitely increasing.
Given that implementing the efficient execution of SQL is a significant engineering effort (and investment), JSON-supporting SQL is obviously seen as an important feature.
As an aside, an intriguing question is: why do database systems that fall into the category of “NoSQL” databases start providing SQL? It appears that the “No” in “NoSQL” is being re-interpreted from “no-SQL” to “no-Relational Model” with JSON-supporting SQL support. One possible answer to the question is the increasing complexity of queries that are being issued. While simple interfaces are good to get going initially, over time, as applications become more involved, complex queries reflect the increasing domain complexity and that is going to be reflected in the query language as well (for a taste, see here: https://realprogrammer.wordpress.com/2014/10/27/oracle-12c-sql-for-json-part-2-basic-queries/ – toward the end of the blog). Another (additional) possible answer is that existing tools (e.g. reporting tools) that are JDBC compliant and can process relational data should be able to connect also.
JSON-supporting SQL Language: Discussions are Ongoing
Discussions about the benefit of JSON-supporting SQL in context of NoSQL databases are coming up repeatedly (e.g., LinkedIn group discussions: https://www.linkedin.com/groups/2085042 (NoSQL Group)). Interesting enough, most of the time, the discussion is not separated or structured by syntax vs. semantics vs. functionality. SQL is developed in context of the relational model and that does not preclude complex data structures like JSON, the opposite is true. Object / relational functionality as well as NF2 work deals with complex structures, including arrays and maps. So it would be prudent, in my mind, to have a separation in the discussion of
- Data structure support (aka, JSON)
- Query functionality
- Query language syntax
I expect the discussion of JSON-supporting SQL to lead towards a declarative language with SQL syntax, based on the relational model, paired with complex data types (e.g. JSON) and a schema-per-object or schema-per-row semantics.
The interesting aspect on the SQL syntax and interpretation is that systems that also support the relational model extend those to support JSON, and these systems will extend SQL, not replace it (as can be seen already).
SQL Support for NoSQL Databases: Best Approach Forward?
In my mind the best approach forward would be standing on the shoulder of giants: take SQL and extend it to support JSON in a very careful but very expressive way, learning from the object / relational work as well as the NF2 work that took place a long time back. Stay true to the learnings of the benefits of a declarative language and the underlying possibility of optimization, be it functional or non-functional. In short, add the domain type JSON and cater for a schema-per-document model.
If JSON support is one of the common functional denominators of most of the databases then the distinction is not really achieved by variations of (JSON-supporting) SQL, but by variations of the non-functional support like scale, throughput, low latency, safety, reliability, etc.
I believe that JSON-supporting SQL will be converging, while the non-functional features will set the various systems further and further apart in order to provide the designed-for sweet spot and establish their competitive advantage.
Clearly, the jury is still out, however, compared to 1 ½ years ago, whispers are emerging that JSON-supporting SQL is becoming an important feature of NoSQL, OldSQL and NewSQL databases.
Clearly, the pendulum swings toward the SQL language grammar and functionality that supports JSON. And that is a good thing in my opinion for the various obvious reasons.
Clearly, discussions keep coming up about JSON-supporting SQL, the various styles and language proposals. This is another reason to believe that SQL support in NoSQL databases and in context of JSON is important.
Let’s check in again into this topic next year some time. It is always interesting to follow such a hotly debated topic and establish checkpoints along the way..
The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.