Over a million developers have joined DZone.

SQL and JSON, what do you think?

DZone's Guide to

SQL and JSON, what do you think?

· Performance Zone
Free Resource

Evolve your approach to Application Performance Monitoring by adopting five best practices that are outlined and explored in this e-book, brought to you in partnership with BMC.

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?


Learn tips and best practices for optimizing your capacity management strategy with the Market Guide for Capacity Management, brought to you in partnership with BMC.


Published at DZone with permission of Anders Karlsson, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.


Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.


{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}