Oracle - Convert Object Types and Collections to JSON

Oracle database has object-oriented programming features that, for example, allow you to store complex object types and even collections of objects (arrays of objects) in a single table column.

JSON is a very popular way to get the same functionality in other databases and applications. Let's see how we can use a PL/SQL function to convert object types and collections to JSON string.

Let'a assume we need to get the following data as the result:

[
  { 
     "code":"SFO",
     "name":"San Francisco International Airport",
     "opened":1927
     "hub":["Alaska Airlines","United Airlines","Virgin America"]
   },
  { 
     "code":"EWR",
     "name":"Newark Liberty International Airport",
     "opened":1928,
     "hub":["FedEx Express","United Airlines"]
   },
  { 
     "code":"SIN",
     "name":"Singapore Changi Airport",
     "opened":1981
     "hub":["Singapore Airlines"]
   }
]

Originally data is stored in the Oracle database, and object types and collections are defined as follows:

Oracle:

-- Airline name
CREATE OR REPLACE TYPE airline AS OBJECT (name VARCHAR2(30));
/
 
-- List of airlines
CREATE OR REPLACE TYPE airline_list IS TABLE OF airline;
/
 
-- Airport
CREATE OR REPLACE TYPE airport AS OBJECT 
( 
  code CHAR(3),
  name VARCHAR2(70),
  opened NUMBER(4),
  hub airline_list
);
/
 
-- List of airports
CREATE OR REPLACE TYPE airport_list IS TABLE OF airport;
/
 
-- Table
CREATE TABLE air_stats
(
    id NUMBER(10),
    data airport_list
) 
  NESTED TABLE data STORE AS nested_data (NESTED TABLE hub STORE AS hub_data);

Now let's insert data into air_stats table:

Oracle:

INSERT INTO air_stats VALUES
(1, 
 airport_list(
   airport('SFO', 'San Francisco International Airport', 1927, 
                 airline_list(airline('Alaska Airlines'), airline('United Airlines'), airline('Virgin America'))),
   airport('EWR', 'Newark Liberty International Airport', 1928, 
                 airline_list(airline('FedEx Express'), airline('United Airlines'))),
   airport('SIN', 'Singapore Changi Airport', 1981, 
                 airline_list(airline('Singapore Airlines')))
    )
);
COMMIT;

Now let's write a PL/SQL user function that we will use to convert our complex object type to a JSON string.

Oracle:

CREATE OR REPLACE FUNCTION airport_list_to_json (data airport_list)
  RETURN VARCHAR2
AS
  json VARCHAR2(4000);
  ap airport;
  hub airline_list;
BEGIN
  json := '[';
  FOR i IN NVL(data.FIRST, 0) .. NVL(data.LAST, -1)
  LOOP
    ap := data(i);
    IF i > 1 THEN
      json := json || ',';
    END IF;
    json := json || '{"code":"' || ap.code || '",';
    json := json || '"name":"' || ap.name || '",';
    json := json || '"opened":' || ap.opened || ',';
    hub := ap.hub;
    json := json || '"hub":[';
    FOR j IN NVL(hub.FIRST, 0) .. NVL(hub.LAST, -1)
    LOOP
      IF j > 1 THEN
        json := json || ',';
      END IF;
      json := json || '"' || hub(j).name || '"';
    END LOOP;
    json := json || ']}';
  END LOOP;
  json := json || ']';
  RETURN json;
END;
/

Now if you apply this function on airport_list complex type, you will get the required JSON string:

SELECT airport_list_to_json(data) FROM air_stats;
 
/* Query result: 
[{"code":"SFO","name":"San Francisco International Airport","opened":1927,"hub":
["Alaska Airlines","United Airlines","Virgin America"]},{"code":"EWR","name":"Ne
wark Liberty International Airport","opened":1928,"hub":["FedEx Express","United
 Airlines"]},{"code":"SIN","name":"Singapore Changi Airport","opened":1981,"hub"
:["Singapore Airlines"]}]
*/

Now having the PL/SQL function that converts complex object types data to JSON, you can use SQLines Data tool to transfer it into any other database.

You need to edit the sqlines_tsel.txt configuration file to specify the SELECT list expression for your table, for example:

Now SQLines Data tool will apply airport_list_to_json PL/SQL function to transform object type values to JSON.