JSON In (and out) of Oracle – JSON Data Type

From the database geek.

Update March 29, 2009:  PL/JSON is now available as an open source project (MIT License).  Read the FAQ at http://oracleoss.com

Do you use JSON? Do you use Oracle? How would you like to use JSON in Oracle? How would you like to store it, generate it and read it? I am creating a new JSON data type that will work like XMLType.

I am assuming you are familiar with Oracle (or you probably wouldn’t be reading this blog). If you are not familiar with JSON it is JavaScript Object Notation, a lightweight data-interchange specification. Think of it as a replacement to XML. Heck, think of it as XML Lite. Data in JSON is smaller than the equivalent XML and, when kept of a reasonable size, is less complex and easier to read. For larger documents and documents needing complex data types, I still go with XML.

A simple JSON object looks something like this (this was validated and formatted by JSONLint):

    "working": {
        "title": "SQL Starter",
        "author": "LewisC",
        "monkeyman": false,
        "age": 21,
        "myarray": [

JSON converts back and forth to XML fairly easily. JSON only has a small number of data types that it supports. You can get the details of it (the entire spec is on one page) at JSON.ORG. The short story is the strings are quoted, members are separated by colon (:) and values can go just about anywhere. Items just to the left of the colon is the name of the member. Items to the right are the member values.

In the example above, the entire thing is a JSON object. Inside of the object is a member: “working”. That member is comprised (:) of another JSON object (the rest of the string). Beneath “working” we have 5 members. Two members are of string data type, the next is boolean and the next is a number and the final one is an array (arrays are delimited with [ ]).

       "title": "SQL Starter",   -- string
        "author": "LewisC",      -- string
        "monkeyman": false,      -- boolean
        "age": 21,               -- number
        "myarray": [             -- array

Items in an array can also be of any data type. In the example above they are all string; they could also be number, boolean or even another JSON object.

Anyway, my little JSON project is at the v0.2 stage so I thought I would write about it. It’s not ready for prime time but it does quite a bit. It generated the JSON object shown above (and can do much more complex documents). Some of the tasks in my roadmap are bi-directional conversion with XML, read and write to OS files and the ability to get a JSON object from a URL. As a TYPE it can already be used as an object table or a column (just like XMLType). If you would like to see certain features, please leave a comment and let me know about it.

Here is the code I used to generate the object above:

     v_json json;
     v_json2 json;
     v_arr_id NUMBER;
     v_ele_id NUMBER;

     -- create a json object
     v_json := json(); 

     -- add some members
     v_json.add_member('title', 'SQL Starter');
     v_json.add_member('author', 'LewisC');
     v_json.add_member('monkeyman', false);
     v_json.add_member('age', 21); 

     -- add a member array
     v_json.add_array('myarray', false, v_arr_id ); 

     -- Add some elements to the array
     v_json.add_array_element( v_arr_id, 'hello', false, v_ele_id );
     v_json.add_array_element( v_arr_id, 'goodbye', false, v_ele_id );
     v_json.add_array_element( v_arr_id, 'kerblooey', false, v_ele_id ); 

     -- create a new json object
     v_json2 := json(); 

     -- assign the first json object to the
     -- second json object as a member
     v_json2.add_member('working', v_json); 

     -- Display the object


Pretty simple, huh? It was fun figuring out how to add a JSON as a member to a JSON. The array was tricky too because an array element can also be a JSON object. I have all of that working now, though. I have a few tweaks and some exception handling to add and then I will release it as v0.5. It can’t yet read JSON objects or XML. It needs to read, at least, JSON (as in importing a document from another system) before I will consider it a v1.0.

BTW, this really is how I spend my free time. 😉


Technorati : , , , , ,

You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.

Comments are closed.