JSON

Creating JSON Based on Hierarchical Query

Jaromir D.B. Nemec

Creating JSON from a database query is a challenging task, but solvable. See [1] as an example of a generation using the LISTAGG function.
The solution is simple in the way it uses only a SELECT statement. On the other side - the number of concatenation pipes in the query is close to the number of Toothpicks in a box.
This post describes a way to create JSON using the Groovy JSON builder. To make it more interesting, we will use a hierarchical query to create the JSON structure.

Set-up

Below is the set-up of the table we will use in our example.

create table hierarchy_tab
(id number not null,
 parent_id number,
 text varchar2(10));
-- fill data
insert into hierarchy_tab values (1,null,'key 1');
insert into hierarchy_tab values (10,1,'key 10');
insert into hierarchy_tab values (101,10,'key 101');
insert into hierarchy_tab values (102,10,'key 102');
insert into hierarchy_tab values (20,1,'key 20');
insert into hierarchy_tab values (201,20,'key 201');
insert into hierarchy_tab values (202,20,'key 202');
commit; 

The whole hierarchy can be queried with the following query:

SELECT id,
  text,
  sys_connect_by_path(id,',') path
FROM hierarchy_tab
  START WITH parent_id IS NULL
  CONNECT BY parent_id  = PRIOR id
;
  
ID                     TEXT       PATH      
---------------------- ---------- ----------
1                      key 1      ,1        
10                     key 10     ,1,10     
101                    key 101    ,1,10,101 
102                    key 102    ,1,10,102 
20                     key 20     ,1,20     
201                    key 201    ,1,20,201 
202                    key 202    ,1,20,202 

Notice that we added the complete path from the root node to the current node to the query (sys_connect_by_path). We will use this expression later.

Groovy Script

In the next step we present the result of this query as a HashMap with the following keys:

id - id of the node
text - descritpion of the node
childern - a List of childer with the same structure.

In the frist place we fill the HashMap with the hierarchical data.

import groovy.sql.Sql
import groovy.json.JsonBuilder
import oracle.jdbc.pool.OracleDataSource
// get connection
def ods = new OracleDataSource();
ods.setURL('jdbc:oracle:thin:@host:1521:SID')
ods.setUser('user')
ods.setPassword('pwd')
/* get Grovvy Sql Connection */
def con = new groovy.sql.Sql(ods)
assert con != null
 
def res = [:]
con.eachRow('''SELECT id,
  text,
  sys_connect_by_path(id,',') path
FROM hierarchy_tab
  START WITH parent_id IS NULL
  CONNECT BY parent_id  = PRIOR id''') 
     { rm ->  
       // split path in a list of keys ignoring the first comma
       def pathLst = rm.path[1..-1].split(',')
     
       if (pathLst.size() == 1)
          // initiate the root node         
          {res = (['id' : rm.id, 'text' : rm.text])}  
       else 
          {  
          // start with the childern of the tree root ...
          if (!res.children) {res.children = []}  // lazy initialize the children list
          def chldLst = res.children
          if (pathLst.size() > 2) {
            // ... and traverse path down, from 2nd element of the list to the 2nd last element
            pathLst[1..-2].each  {search_id ->
             def tmp = chldLst.find{it.id == search_id}
             if (!tmp.children) {tmp.children = []}
             chldLst = tmp.children
         }  
        }
        chldLst << (['id': rm.id, 'text' : rm.text])       
       }
     }
println "result $res" 

This produce the following result (formatted):

result 
[id:1, text:key 1, 
children:[[id:10, text:key 10, 
           children:[[id:101, text:key 101], 
                     [id:102,text:key 102]]], 
          [id:20, text:key 20, 
           children:[[id:201, text:key 201], 
                     [id:202, text:key 202]
]]]]

Finally we will use the JSON builder to produce the JSON file

def json = new JsonBuilder(res)
println json.toPrettyString() 

Notice also that the pretty printig function is activated.

{
    "id": "1",
    "text": "key 1",
    "children": [
        {
            "id": "10",
            "text": "key 10",
            "children": [
                {
                    "id": "101",
                    "text": "key 101"
                },
                {
                    "id": "102",
                    "text": "key 102"
                }
            ]
        },
        {
            "id": "20",
            "text": "key 20",
            "children": [
                {
                    "id": "201",
                    "text": "key 201"
                },
                {
                    "id": "202",
                    "text": "key 202"
                }
            ]
        }
    ]
} 

[1] http://technology.amis.nl/2011/06/14/creating-json-document-straight-from-sql-query-using-listagg-and-with-clause/

Jaromir D.B. Nemec is a freelancer specializing in data warehouse and integration solutions. He can be reached under http://www.db-nemec.com