[Update 2015 July 09]

At last 10 years after XML native support, Microsoft is announcing native JSON support in its next version of SQL Server 2016. Which is nice. It is shame PostgreSQL already has pretty decent native JSON since 2010. Ah sorry … I just can’t resist “snarky” comments.

[Update 2015 Mar 18]

Found today an interesting link. Apparently ORACLE does JSON.

[Started on 2008 Nov 4-th.]

This text originates from me, Dusan B. Jovanovic. Any similarities found elsewhere, are purely accidental. It, and any attachments, do contain confidential information and are subject to copyright and other intellectual property rights. It is only for the use of you, the reader. You may not copy, forward, disclose, save or otherwise use it in any way if you are not the the intended reader or responsible for delivery. If you receive this text by mistake, please advise me, and erase it immediately. I (Dusan B. Jovanovic) may monitor the content of texts and emails within my network to ensure compliance with the  policies and procedures. Any email or text is susceptible to alteration and its integrity cannot be assured. I (Dusan B. Jovanovic) shall not be liable if the text or message is altered, modified, falsified, or edited.


Release 1.5

©2008-2009-2016 by Dusan B. Jovanovic

First of all: Why?

DBJSON™: Provides syntax and protocol, for the two-way communication, with the database in the form of a stream of JSON objects. DBJSON is JSON “formatted” Database Interface. For talking to the RDBMS, DBJSON replaces SQL. DBJSON is JSON interface to the data based online services. DBJSON is not SQL native DB extension to return (or index) JSON strings and data types.

You already forgot XML, now forget SQL. Think JSON.

SQL is the scripting language of talking to databases. To the caller (user), SQL enabled DB server does not return SQL. Instead it returns a pointer into the binary result structure (aka: cursor). In essence an memory block of text. The SQL based communication is a text based protocol. Ok then, what seems to be the problem?

Here we have one key issue: Transformations. Transformation chain, is always required to transform to/from SQL and Result pointer, and applications using the RDBMS.

Worse. In case of web app’s a whole sequence of transformations is required. Here are the two common implementations and their Run-Time transformations “From the data to the HTML”:

DB –> SQL –>T1–> ADORecordSet –>T2–> JavaScript == HTML
DB –> SQL –>T3–> ServiceXML –>T4–> AJAX == HTML

Flow from left to right is the data flow. SQL text queries are issued from either ADO (that was “the way” long time ago) or Service, both on the server side. Somewhere in the “middle tier”. We have two chains: request to the DB and reply from the DB. Thus each transformation chain requires (at least 2 transformations).

  • T1 — transform from SQL to ADO (or simillar) recordset (or .NET dataset)
  • T2 — transform from recodset/dataset to the javascript
  • T3 — transform from SQL to XML (sometimes can use native SQL ‘AS XML’ mechanism)
  • T4 — transform from XML to javascript

That is a lot of code to go wrong. Now imagine for example, an SQL query result delivered as a set of DBJSON™ objects. Where DBJSON (JavaScript) is an inbuilt (aka: native) RDBMS etension, to return the result as a set of JSON objects. In a simple string.


This is just a output from the DB. In this imaginary world , DB + DBJSON + AJAX , chain would be extremely simple compared to current Result Pointer to HTML juggling through sequence of transformations. While there are no transformations present on the diagram above. AJAX is consuming DBJSON as any other JSON formated strings: instantly. And since DB returns DBJSON formated text, Service is just passing it to he waiting caller. In the shape of the AJAX components on the client side. Of course all the security issues are solved at the point of Service.

RDBMS result pointer is this special binary entity. Which is essentialy in-memory block of text lines. Thus DB result “cursor” (standard binary DB output structure), DB can not return full set of related DB entities all together with relations described. This is why (as an known example) .NET DataSet was invented.  Opposit to this, DBJSON™  full DB SET (relations description included) would be (is?) natural and easy to implement.

It should be possible to  add DBJSON™ facade/mediator component, to any legacy RDBMS. Thus to bypass DB rezult “cursor” transformations completely.

It has to be said, this is exactly why countless OO wrap-ups have been invented for, like ADO, OLEDB,  JDBC, etc … DBJSON is not very far from the same aim. But it is fundamentally different. It replaces SQL , it is completely different paradigm of communicating with data base. And last but not the least it is JavaScript.

6 Replies to “SQL to JSON: SELECT * FROM persons AS JSON”

  1. I Like the idea very much. I think the bigger problem in the short term is that web browsers aren’t secure so you couldn’t use DBJSON to expose your database directly or else anyone could run any query they wanted to by hacking the JavaScript. It could work out of the box with Aptana’s Jaxer, with Rhino, or maybe even with Adobe AIR where the JavaScript runtime is more in the developers control. I’ll be interested to see how this goes.

  2. Thanks Ian,

    Text currently (05Nov08) on the blog contains a good idea but is shamefully chaotic, I know.

    The comment you made on the security is of course valid. Answering it properly opens a “can of worms” which is called : Server Side JavaScript ;o)

    I certainly would not allow my team to send SQL select statements from a browser page back to the RDBMS.
    But server side components written with JavaScript are an entirely different matter. Also.
    I can easily envisage an architecture where client-to-server selections are totally removed. And client-to-client_data selections are totally ok. Like TAFFY does.

    Interesting near future development are client side DB-s, full blown but lightweight , like SQL CE 3.5 SP1, etc … This is where client app talks to the local DB which is transparently synchronized with separate infrastructure with a DB back end, over the wire. I can immediately (today) write AJAX app which will run inside HTA pages and talk to the local SQLSVR CE3.5. Only DBJSON mediator is missing ;o)

    PS: DBJSON can have a secondary but still usefull role here: as an security broker/mediator.

    Regards: Dusan

    1. Zoom to March 2016: Who would think that NODE.JS would look so uncertain and distant, way back in 2008?

      But ok, I was not very far of the mark with “Server Side JavaScript” 🙂

  3. Very true. In fact I think Server Side JavaScript with tools like this has a really bright future and makes a lot of sense. Looking forward to seeing how this goes.

  4. @shadowbq : Thanks for your somewhat terse comment ;o)

    JavaScript in “CouchDB” is partial implementation of the “MapReduce” concept and framework.
    ( http://en.wikipedia.org/wiki/MapReduce )
    And on “MapReduce”, I am affraid I largely agree with David DeWitt and Michael Stonebraker, pioneering experts in parallel databases and shared nothing architectures. In short: thumbs down.

    I am talking here about ubiquitous add on to current SQL syntaxes inside RDBMS-es, which would be a key to people adopting and understanding JSON.

Comments are closed.