Generating JSON_OBJECT with SQL functions in Oracle 12c

Oracle added support for JSON with the respective version Oracle 12C. The added support can be divided into :

Generation of JSON Data
Store and Manage JSON Data
Insert, Update and Load JSON Data
Query JSON Data

JSON data and XML data can be used in Oracle Database in similar ways. Unlike relational data, both can be stored, indexed, and queried without any need for a schema that defines the data. Oracle Database supports JSON natively with relational database features, including transactions, indexing, declarative querying, and views.

In our usecase we are going to focus on generating JSON data for migration purposes out of Oracle DB Data.

You can generally use SQL code, including SQL code that accesses JSON data, within PL/SQL code. You cannot use an empty JSON field name in any SQL code that you use in PL/SQL.

The following SQL/JSON functions and conditions are also available as built-in PL/SQL functions: json_value, json_query, json_object, json_array, and json_exists. (In PL/SQL, SQL condition json_exists is a Boolean function.)

To generate JSON object out of Oracle DB data following new built-in functions can be used :

json_object – constructs JSON objects from name–value pairs. Each pair is provided as an explicit argument. Each name of a pair must evaluate to a SQL identifier. Each value of a pair can be any SQL expression. The name and value are separated by keyword VALUE.
json_array – constructs a JSON array from the results of evaluating its argument SQL expressions. Each argument can be any SQL expression. Array element order is the same as the argument order.
json_objectagg – constructs a JSON object by aggregating information from multiple rows of a grouped SQL query as the object members.
json_arrayagg – constructs a JSON array by aggregating information from multiple rows of a grouped SQL query as the array elements. The order of array elements reflects the query result order, by default, but you can use the ORDER BY clause to impose array element order.

We have a usecase, where we are asked to migrate specific customer information from CRM System Oracle Siebel to new system via json files. We will use new JSON SQL function to achieve this.

The Table S_CONTACT contains customer data and we are asked specifically to extract marketing relevant data SUPPRESS_CALL_FLG, SUPPRESS_EMAIL_FLG, SUPPRESS_MAIL_FLG.

These marketing flag are to be in the array [] json element for which we may use json_array. Every JSON element is to be created by json_object function.

In the normal SQL relation DB world the query looks like :

SELECT C.CSN,
       NVL(C.SUPPRESS_CALL_FLG,'N'),
       NVL(C.SUPPRESS_MAIL_FLG,'N'),
       NVL(C.SUPPRESS_EMAIL_FLG,'N'),
NVL(C.X_SUPPRESS_RAMA_FLG,'N')       
 FROM siebel.S_CONTACT C

To build a json message for the export, following SQL statement is to be used :

 select json_object 
('requestInformation' value 'export marketing customer',
 'identifier' VALUE csn,
 'purposes' VALUE json_array(json_object('name' value 'NO_CALL','TransactionType' value decode(CON.SUPPRESS_CALL_FLG,'Y','NO_OPT_OUT','OPT_OUT')),
                             json_object('name' value 'NO_EMAIL','TransactionType' value decode(CON.SUPPRESS_EMAIL_FLG,'Y','NO_OPT_OUT','OPT_OUT')),
                             json_object('name' value 'NO_MAIL','TransactionType' value decode(CON.SUPPRESS_MAIL_FLG,'Y','NO_OPT_OUT','OPT_OUT'))))
from siebel.s_contact con,siebel.s_party par
where 
con.par_row_id = par.row_id and con.PRIV_FLG='N' and par.PARTY_TYPE_CD <> 'Suspect' and con.CUST_STAT_CD = '1' and con.X_CAMP_CON_FLG = 'N' and con.EMP_flg <> 'Y' and con.CON_CD = 'Partner' 
and CON.CSN IS NOT NULL  and CON.X_SOURCE = '100'

Results ma look like :

{"requestInformation":"export marketing customer","identifier":"P-XXXX-1212","purposes":[{"name":"NO_CALL","TransactionType":"OPT_OUT"},{"name":"NO_EMAIL","TransactionType":"OPT_OUT"},{"name":"NO_MAIL","TransactionType":"OPT_OUT"}]}
{"requestInformation":"export marketing customer","identifier":"P-AAAA-BBBB","purposes":[{"name":"NO_CALL","TransactionType":"OPT_OUT"},{"name":"NO_EMAIL","TransactionType":"OPT_OUT"},{"name":"NO_MAIL","TransactionType":"OPT_OUT"}]}
{"requestInformation":"export marketing customer","identifier":"P-4444-3333","purposes":[{"name":"NO_CALL","TransactionType":"OPT_OUT"},{"name":"NO_EMAIL","TransactionType":"OPT_OUT"},{"name":"NO_MAIL","TransactionType":"OPT_OUT"}]}

The results can be exported in the sql editor or the PL/SQL procedure can be extended by writing the result into file system using UTL_FILE library.

In this example we have used native Oracle 12c JSON Object built-in functions to generate JSON base export file.

For JSON Queries or inserting , updating and loading JSON Data there is a good oracle documentation. It is always advantage use buil-in functions to ensure generate valid JSON document and focus on logic and functionality rather to synthetize JSON by puting together string.