Sunday, May 26, 2013

SQL and JSON, what do you think?

As you might know, I'm a big fan of JSON. One big reason is that I believe that JSON is closer to most developers view on data, whereas the Relational SQL based model is closer to what someone working with data itself or someone working with infrastructure. What I mean here is that neither view is wrong, but they are different.

So, given that, can we merge the Object JSON world with the relational model? Well, not JSON, but Hibernate does it quite well. This is one of my objects to the NoSQL world, that the datamodel is closely linked to the application at hand, and less so to data itself and to other applications. Stuff such as accounts, privileges, accounting data, orders and many other things are global, and are not specifically connected a specific application, but in many NoSQL applications, this is what it ends up being.

And there are not that many good solutions, how can I easily explore data in a NoSQL database, where the data model is application based, without me knowing the application? See what a high-ranking 10gen person, Dwight Merriman (Chairman of the board) has to say on the subject in an interview in 2011.

On the other hand, we relational folks has a few things to answer to. We insist that we know how data is to be managed (and I think we do) and we seem to ignore the fast that development tools and environments has changed sine the 1980s (we still insist that proprietary client/server protocols is the way to go, and that SQL rules), In my mind, SQL and Relational rules for data, but not necessarily for applications, and we (including yours truly) should wake up and appreciate what is going on here: We need better, more developer focused, means of accessing data, even if that data is relational data.

The NoSQL camp is getting ready to fix what they can fix: proper query languages (Cassandra CQL being one), better data access methods and probably much more that I am not aware of right now.

Which bring me to what we can do to be more developer friendly on the SQL and relational side of things. And the answer is, as I have already hinted, JSON support! MariaDB has a few trucks up it's sleeve, like the Dynamic Columns support. But this is nowhere near enough, the idea is OK but the way to use dynamic columns for a developer doesn't help much, rather the interface is more targeted at making the Cassandra Storage Engine features easier to access from the MariaDB SQL Interface. But I'll do a blog later on the Dynamic Column support and JSON later on anyway.

What I would want, but maybe that's just me, is a proper JSON datatype, with JSON functions, syntax and semantics. That is a starting point I think, and it shouldn't be too difficult. But many other JSON related things that we could do spring to mind:

  • SQL / JSON language mix - What I mean here is that JSON isn't just a string datatype, but that we can include plain JSON, where approprite, in the SQL:
    INSERT INTO table VALUES(57, {"empno": 1, "ename": "Scott})
  • JavaScript stored procedures
  • ROW data as JSON support in the MySQL API
  • JSON column data as ROW data in the MySQL API and tools.
  • A JSON Storage Engine
  • JSON support in LOAD DATA INFILE
  • JSON support in SELECT .. INTO OUTFILE
  • REST / JSON interface to complement the MySQL interface
  • JSON attribute indexing
A then again some stuff I haven't though of so far. A reason for this blogpost if to get your opinion? What do you think? Would this be useful? Would is make MySQL easier to use?

And example why this is useful: instead of having to add columns to a table for some new attribute, attributes that are useful mostly for the applications, is then done by just adding the to a JSON column in the table and the application can add what it wants to that column. We have all seen this, haven't we? Some odd column that the application wants to keep track of in some table, some columns the data is which is pretty uninteresting as data, but is still necessary by the application? This is where a proper JSON type would help?  Also, the MariaDB Dynamic Columns help with that, but I think JSON would be a far more elegant solution?

/Karlsson

1 comment:

Nicolas FROIDURE said...

As a developper, i don't want to see JSON in SQL queries.

On the other hand, helpers to load JSON, wrappers to GET database results from a Rest interface sounds good.

The only question remaining is : is the database the place for that ?

I guess no cause this work can be done under the database abstraction layer for more than only one database server.

In any case, if MySQL decide to do so, it will be nice to have an option to disable it.

I think you're right, developpers use NoSQL engines when unnecessary cause those engines are closest to the way they think.

But, experimented developpers prefers ACID and SQL standard than ease of use and i assume most of the beginners working with NoSQL database will come back to relationnal ones. In fact, i know some guys that already did it.

Would be sad if a temporary (for me) overuse of NoSQL databases were leading to a classic relationnal open-source databases defacing.