Convert Relational Data to JSON in Oracle: PL/JSON Method

December 5, 2017

in JSON, PL/SQL, Scripts

In the JSON series so far, we’ve talked about how to store JSON data in Oracle and apply JSON conditional checks, and how to query JSON data and convert it to relational form. What if you want the opposite i.e. to convert relational data to JSON form? That’s doable too – let’s see how.

Convert Relational Data to JSON

What you have: a relational table in Oracle.

What you want: data extracted from said relational table in JSON format.

How do you do it?

Make use of the open source package PL/JSON for a simple way to get JSON output.

What is PL/JSON?

PL/JSON is an open source PL/SQL utility for working with JSON in Oracle. Its host site GitHub describes it as:

…a generic JSON object written in PL/SQL. Using PL/SQL object syntax, users instantiate a JSON object and then add members, arrays and additional JSON objects. This object type can store JSON data, in Oracle, persistently.

Before You Begin: Install PL/JSON in Oracle

To convert relational data to JSON using PL/JSON, you would first need to install PL/JSON in your database. Here are the steps for doing so.

1. Download the PL/JSON installable

From https://github.com/pljson/pljson, download the ZIP file for installing PL/JSON.

Download-PLJSON-Installable

2. Extract PL/JSON to a local folder

PLJSON-ExtractedZIPExtract the downloaded ZIP file to a local folder. This will create a folder structure containing all the files and sub-folders required for PL/JSON installation. At the time of writing this article, the files are extracted under a sub-folder called pl-jsonmaster, with contents as shown alongside.

3. Run install.sql

From command prompt, navigate to the directory containing install.sql. Login to sqlplus and run install.sql.

install.sql will internally invoke the required scripts to create objects for PL/JSON installation.

SQL> @install.sql
-- Setting optimize level -

Session altered.

-----------------------------------
-- Compiling objects for PL/JSON --
-----------------------------------

PL/SQL procedure successfully completed.


Type created.


Type created.

No errors.

Type created.

No errors.

Type created.

No errors.

Package created.


Package body created.


Package created.


Package body created.


Type body created.

No errors.

Package created.


Package body created.


Type body created.

No errors.

Type body created.

No errors.

Package created.


Package body created.

------------------------------------------
-- Adding optional packages for PL/JSON --
------------------------------------------

Package created.


Package body created.


Package created.


Package body created.


Package created.


Package body created.


Package created.


Package body created.


Package created.


Package body created.


Type created.


Type created.


Type created.


Synonym created.


Synonym created.


Type created.


Type body created.

SQL>

PL/JSON is now configured for converting relational data to JSON.

Case Study: Source and Target

Source table custrel stores the customer’s id (primary key), name and a set of contacts — Mobile, Home Phone, Alternate Home Phone, Office Phone. All contact information is optional. The database mappings are:

Mobile : mobile
Home Phone : homeph
Alternate Home Phone : homephalt
Office Phone : officeph

The data in the source table looks like this:

SQL> select * from custrel;

CUSTID CUSTNAME MOBILE         HOMEPH         HOMEPHALT      OFFICEPH
------ -------- -------------- -------------- -------------- --------------
1      Susan    (555) 555-1234 (651) 399-9262                (651)-555-1999
2      Martin   (555) 321-4311 (627)-444-2888 (627) 587-6623

The aim:

  • produce JSON output containing contacts for an input customer id
  • if the input customer id is not valid, return null
  • if the contact value is unspecified, exclude the name-value pair from the output JSON

Target JSON for custid 1:

{
 "Mobile":"(555) 555-1234"
,"HomePh":"(651) 399-9262"
,"OfficePh":"(651)-555-1999"
}

Target JSON for custid 2:

{
"Mobile":"(555) 321-4311"
,"HomePh":"(627)-444-2888"
,"HomePhAlt":"(627) 587-6623"
}

Approach: PL/SQL Code to Transform Relational —> JSON

In essence, the solution approach is:

1. Create a function get_custjson which takes as input a custid, and returns a CLOB containing the JSON output for the customer.

2. Invoke the function get_custjson from SQL.

Function code and run-time output follows…

Solution Details and Run-Time Output

1. Create a function get_custjson which takes as input a custid, and returns a CLOB containing the JSON output for the customer.

-- Function to generate JSON data from relational data
-- Based on PL/JSON
create or replace function get_custjson(
   p_custid in custrel.custid%type
)
   return clob
is
 
   l_custrel_rec         custrel%rowtype;
   l_metadata_json       json;
   l_metadata_json_clob  clob;
 
begin
 
  -- Get relational data
  select *
  into l_custrel_rec
  from custrel
  where custid = get_custjson.p_custid;
   
  -- Initialize JSON object   
  l_metadata_json := json();
  
  -- Populate JSON object using relational data
  -- Add a name-value pair to the JSON only if value exists
  if (l_custrel_rec.mobile is not null) then 
    l_metadata_json.put('Mobile', l_custrel_rec.mobile); 
  end if;
  
  if (l_custrel_rec.homeph is not null) then
    l_metadata_json.put('HomePh', l_custrel_rec.homeph); 
  end if;
  
  if (l_custrel_rec.homephalt is not null) then 
    l_metadata_json.put('HomePhAlt', l_custrel_rec.homephalt); 
  end if;
  
  if (l_custrel_rec.officeph is not null) then
    l_metadata_json.put('OfficePh', l_custrel_rec.officeph); 
  end if;
  
  -- Convert to CLOB and return
  dbms_lob.createtemporary(l_metadata_json_clob, true);
  l_metadata_json.to_clob(l_metadata_json_clob);
  return l_metadata_json_clob;
   
exception

   when no_data_found then
     return null;

end get_custjson;

When run:

SQL> -- Function to generate JSON data from relational data
SQL> -- Based on PL/JSON
SQL> create or replace function get_custjson(
  2     p_custid in custrel.custid%type
  3  )
  4     return clob
  5  is
  6
  7     l_custrel_rec         custrel%rowtype;
  8     l_metadata_json       json;
  9     l_metadata_json_clob  clob;
 10
 11  begin
 12
 13    -- Get relational data
 14    select *
 15    into l_custrel_rec
 16    from custrel
 17    where custid = get_custjson.p_custid;
 18
 19    -- Initialize JSON object
 20    l_metadata_json := json();
 21
 22    -- Populate JSON object using relational data
 23    -- Add a name-value pair to the JSON only if value exists
 24    if (l_custrel_rec.mobile is not null) then
 25      l_metadata_json.put('Mobile', l_custrel_rec.mobile);
 26    end if;
 27
 28    if (l_custrel_rec.homeph is not null) then
 29      l_metadata_json.put('HomePh', l_custrel_rec.homeph);
 30    end if;
 31
 32    if (l_custrel_rec.homephalt is not null) then
 33      l_metadata_json.put('HomePhAlt', l_custrel_rec.homephalt);
 34    end if;
 35
 36    if (l_custrel_rec.officeph is not null) then
 37      l_metadata_json.put('OfficePh', l_custrel_rec.officeph);
 38    end if;
 39
 40    -- Convert to CLOB and return
 41    dbms_lob.createtemporary(l_metadata_json_clob, true);
 42    l_metadata_json.to_clob(l_metadata_json_clob);
 43    return l_metadata_json_clob;
 44
 45  exception
 46
 47     when no_data_found then
 48       return null;
 49
 50  end get_custjson;
 51  /

Function created.

2. Invoke the function get_custjson from SQL

-- Get JSON output by calling get_custjson from SQL
-- Input  : custid
-- Output : JSON document of customer contacts

select a.custname, get_custjson(a.custid) custmetadata 
from custrel a
where a.custid = 1;

select a.custname, get_custjson(a.custid) custmetadata 
from custrel a
where a.custid = 2;

When run:

SQL> -- Get JSON output by calling get_custjson from SQL
SQL> -- Input  : custid
SQL> -- Output : JSON document of customer contacts
SQL>
SQL> select a.custname, get_custjson(a.custid) custmetadata
  2  from custrel a
  3  where a.custid = 1;

CUSTNAME CUSTMETADATA
-------- ----------------------------------------
Susan    {"Mobile":"(555) 555-1234"
         ,"HomePh":"(651) 399-9262"
         ,"OfficePh":"(651)-555-1999"
         }

SQL> select a.custname, get_custjson(a.custid) custmetadata
  2  from custrel a
  3  where a.custid = 2;

CUSTNAME CUSTMETADATA
-------- ----------------------------------------
Martin   {"Mobile":"(555) 321-4311"
         ,"HomePh":"(627)-444-2888"
         ,"HomePhAlt":"(627) 587-6623"
         }

Good News: This works pre-12c too!

Oracle’s support for JSON – SQL/JSON or IS JSON check, for example – starts release 12c onwards. In Oracle 11G, there’s no JSON_TABLE to map a CLOB-based JSON into relational rows and columns.

PL/JSON though is not constrained by 12c JSON features. It works merrily with Oracle 11G, making use of Oracle’s object-oriented features to generate JSON from relational data without a fuss.

Summary

This article shows you how to construct JSON documents from relational data using PL/JSON.

For more in the JSON series, also read:

There are other more sophisticated ways to convert relational data to JSON too — to know more about those I’ll refer you to this excellent write-up.

Leave a Comment

Previous post:

Next post: