Right Outer Join

18 July 2014

Hierarchical JSON from Oracle

Filed under: Master Data Management, Oracle — Tags: , , , — mdahlman @ 14:03


Semarchy manages master data hierarchies (corporate structures, product group hierarchies, employee management trees, etc.) easily with out of the box functionality. By this I mean it can validate the data, match up different sources, enrich the data from external systems, manage an audit log of changes and so forth. It’s all great stuff.  But on a recent project  I wanted to display hierarchical data using an intuitive visual interface. A plethora of visualization libraries exist, and I was leaning toward using D3 since it appears to be one of the most polished, most used, and most actively developed at the moment.


The D3 example I wanted to use is designed to accept data in JSON format. My data is in Oracle, and Oracle doesn’t provide a simple way to generate a complex JSON output.

Likely Solutions

A few people pointed me to plsql-utils, aka Alexandria, as the best starting point. It’s a really useful Oracle resource, and I spent some time investigating this idea. Morten Braten’s 2010 article about it is excellent. But in the end I didn’t find it to be the right tool for this problem. It made it very easy to take a result set and convert it to valid JSON where each row was a JSON record. But this was just tabular data as JSON, so this was not what I needed to feed into the D3 engine. I have no doubt that I could write a stored procedure which could loop through my data to get a more appropriate hierarchical structure and then use plsql-utils to convert this to JSON. But the level of effort required seemed high.

I found several references to PL/JSON. This project shows potential, but it doesn’t appear to be actively developed (as of mid 2014).

There’s an interesting answer at the greatest of all answer sites from Mike Bostock, the author of D3(!). That example is focused on converting comma separated values (CSV) data to JSON. The concepts could be applied here. But my data, though tabular, is not actually CSV. I would prefer to use the D3 sample with the smallest number of changes possible. So I would much prefer to return the data to D3 already JSON-ified if I can.

Then I found Lucas Jellema’s 2011 article about generating a JSON string directly from a query. This presented a more intuitive approach for me. He uses a common table expression (CTE) to easily create a sub-select to gather the hierarchical information along with the LIST_AGG analytic function to present it well. Clever. In the end I didn’t actually use LIST_AGG and I didn’t really use a CTE. (OK, my sample query below has a CTE… but it could be changed into a standard subquery with trivial effort.)

My Solution

In the end I decided to use Oracle’s inherent abilities to handle hierarchical information (mainly the CONNECT BY syntax) and then convert it to JSON with the additional of simple string logic. The key concepts needed in this conversion are:

  • The CONNECT BY query can return the data in a specified logical order.
  • By knowing if the next record is at a higher, lower, or equal level in the hierarchy, we can generate JSON brackets correctly.
  • We can know if the next record is at a higher, lower, or equal level in the hierarchy by using analytic windowing functions like LAG and LEAD.

Here’s the commented SQL used to return the data

WITH connect_by_query as (
     ROWNUM                               as rnum
    ,FIRST_NAME || ' ' || LAST_NAME       as FULL_NAME
    ,LEVEL                                as Lvl
    /* the top dog gets a left curly brace to start things off */
    WHEN Lvl = 1 THEN '{'
    /* when the last level is lower (shallower) than the current level, start a "children" array */
    WHEN Lvl - LAG(Lvl) OVER (order by rnum) = 1 THEN ',"children" : [{' 
    ELSE ',{' 
  || ' "name" : "' || FULL_NAME || '" '
  /* when the next level lower (shallower) than the current level, close a "children" array */
  || CASE WHEN LEAD(Lvl, 1, 1) OVER (order by rnum) - Lvl <= 0 
     THEN '}' || rpad( ' ', 1+ (-2 * (LEAD(Lvl, 1, 1) OVER (order by rnum) - Lvl)), ']}' )
from connect_by_query
order by rnum;

Here’s an example of the data returned (formatting was added afterwards, but the data was returned exactly like this):

  "name": "Steven King",
  "children": [{
    "name": "Neena Kochhar",
    "children": [{
      "name": "Nancy Greenberg",
      "children": [{
        "name": "Daniel Faviet"
      }, {
        "name": "John Chen"
      }, {
        "name": "Ismael Sciarra"
      }, {
        "name": "Jose Manuel Urman"
      }, {
        "name": "Luis Popp"
  }, {
    "name": "Lex De Haan",
    "children": [{
      "name": "Alexander Hunold",
      "children": [{
        "name": "Bruce Ernst"
      }, {
        "name": "David Austin"
      }, {
        "name": "Valli Pataballa"
      }, {
        "name": "Diana Lorentz"
  }, {
    "name": "Den Raphaely",
    "children": [{
      "name": "Alexander Khoo"
    }, {
      "name": "Shelli Baida"
    }, {
      "name": "Sigal Tobias"
    }, {
      "name": "Guy Himuro"
    }, {
      "name": "Karen Colmenares"

With the data in that form, it was easy to implement this D3 sample inside Semarchy Convergence for MDM:

Employee Hierarchy in Semarchy MDM

The left side shows the standard tree view. Practical.
The right side shows the D3 tree visualization. Awesome.
(And practical in different ways.)


Here is the SQL (creates, inserts, and the complete select statement) to try it yourself:

Oracle select query to generate JSON data



7 May 2013

REST endpoint for Jaspersoft Query Executor

Filed under: JasperReports Server — Tags: , , , — mdahlman @ 10:58

Show me the data

Often, usually, nearly-always I want my report server to give me a nicely formatted view of my data. A “report”. But not always. Sometimes I just want my data. I’ll format it myself, thank you. JasperReports Server can do that, but I had never done it before. Here are my notes from figuring it out.


  • JasperReports Server v5.1 or later is installed and working.
  • A data source and a domain are defined.
  • I have at least one query written to get started with.
  • (If you just want to follow along, you can use the sample domain included with JRS and the sample query provided below.)
  • A REST Client for testing. Use whatever you like. I recommend Advanced REST Client (a Chrome plugin).

Construct the URL

Here is the basic structure of the URL we need to create:


In my case I’ll use the sample domain “Supermart Domain”. The URI path is “/public/Samples/Domains/supermartDomain”

Domain URI path

The domain URI path is part of its properties

I constructed a simple query using the Domain Expression Language (domEL) syntax. Refer to the JasperReports Server User Guide for details. Here’s the query in readable form:

    <queryField id="inv_store.inv_store__store_name" />
    <queryField id="inv_store.inv_store__store_type" />
    <queryField id="inv_store__store_contact.inv_store__store_contact__store_manager" />
  <queryFilterString>inv_store__region.inv_store__region__sales_state_province == 'CA'</queryFilterString>

Here’s the same query with spaces and carriage returns removed and url encoded:


With the domain and the query identified, now I have everything I need to construct the url:


I’m ignoring authentication. For a quick test you can first login to JRS, then paste in this url. You’ll get a response in the default format: XML. Refer to the JasperReports Server Web Service Guide for details on how to securely authenticate programmatically.

Show me the data

But I want JSON rather than XML. No problem; I just add the header “Accept: application/json” to my request. Now my result set comes back in JSON.

This screenshot from the Advanced Rest Client shows the added header and the JSON result set.

This screenshot from the Advanced Rest Client shows the added header and the JSON result set.

Why is this cool? Now I have complete flexibility to render the data as I like, but I still get the benefits of the report server:

  • Security: I’ll only see the data that I should see)
  • Scalability: JRS does intelligent data caching
  • Consistency: I’ll continue to use the same data source for most of my data visualizations which get rendered as reports on the server.

Blog at WordPress.com.