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.
2. Extract PL/JSON to a local folder
Extract 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:
- Why you should store JSON data in the database
- Ways to query JSON data in Oracle
- Conditional checks for JSON in Oracle
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.
