Serializing/marshalling package level user define record types to JSON

Question

I'm trying to convert a record type, defined in a PL/SQL package, to JSON.

I found that in SQL, I can use select json_object(*) from SomeTable, to return objects that have a property for each column in the table, but I can't seem to do this with record types in PL/SQL code.

Example package with types and a function to return (serialize) json based on a type:

create or replace package Customer as
  type RT_Address is record (
    Line1 varchar2(100),
    Line2 varchar2(100),
    City varchar2(100)
  );

  type RT_Customer is record (
    FirstName varchar2(100),
    LastName varchar2(100),
    Address RT_Address
  );

  function AsJson(P_Customer RT_Customer)
  return varchar2;

end;

create or replace package body devvanessa.Customer as

  function AsJson(P_Customer RT_Customer)
  return varchar2 is
    V_DOM jdom_t;
    V_JSON json_object_t;
    V_JSONBody varchar2(4000);
  begin

    V_JSON := json_object(P_Customer); -- PLS-00103: Encountered the symbol when expecting one of the following: . ( * @ % & - + / at mod remainder rem <een exponent (**)> || multiset value

    if V_DOM.append(P_CUSTOMER) then -- PLS-00306: wrong number or types of arguments
      null;
    end if;
    V_JSONBody := V_Json.STRINGIFY;

    return V_JSONBody;
  end;

end;

The above is slightly simplified, because I actually want to store this json and do some other things with it, but it does show the core of my problem:

How do I convert a record type to Json in PL/SQL, without specifying all individual fields separately. I'm also curious how it would work the other way around.

I've been searching various sources, like documentation on JSON functions, Oracle 19's JSON documentation, and simply the code completion hints I got on the json_object_t and jdom_t types, but so far I can't find any evidence that it's possible at all.

This would work:

    V_JSon.Put('FirstName', P_Customer.FirstName);
    -- repeated for each property

This way I get json, but it requires me to specify each field individually.

Answer 1

JSON_OBJECT_T does not have a constructor that takes in a record type so you will need to explicitly define each of the keys/values to define the JSON. JDOM_T is not required for what you are attempting to do. Below is an example on how to convert your record types to JSON and back.

DECLARE
    TYPE RT_Address IS RECORD
    (
        Line1    VARCHAR2 (100),
        Line2    VARCHAR2 (100),
        City     VARCHAR2 (100)
    );

    TYPE RT_Customer IS RECORD
    (
        FirstName    VARCHAR2 (100),
        LastName     VARCHAR2 (100),
        Address      RT_Address
    );

    l_customer1   rt_customer
        := rt_customer ('John', 'Doe', rt_address ('123 Main Street', 'Apartment# 2A', 'London'));
    l_customer2   rt_customer
        := rt_customer ('Jane', 'Smith', rt_address ('456 Broken Dreams Blvd', NULL, 'Greenville'));
    l_json        json_object_t;
    l_record      rt_customer;

    FUNCTION customer_record_to_json (P_Customer RT_Customer)
        RETURN json_object_t
    IS
        l_address    json_object_t := json_object_t ();
        l_customer   json_object_t := json_object_t ();
    BEGIN
        l_address.put ('Line1', p_customer.address.line1);
        l_address.put ('Line2', p_customer.address.line2);
        l_address.put ('City', p_customer.address.city);

        l_customer.put ('FirstName', p_customer.firstname);
        l_customer.put ('LastName', p_customer.lastname);
        l_customer.put ('Address', l_address);

        RETURN l_customer;
    END;

    FUNCTION customer_json_to_record (p_customer_json json_object_t)
        RETURN rt_customer
    IS
        l_address_json   json_object_t := json_object_t ();

        l_address        rt_address;
        l_customer       rt_customer;
    BEGIN
        l_address_json := p_customer_json.get_object ('Address');
        l_address.line1 := l_address_json.get_string ('Line1');
        l_address.line2 := l_address_json.get_string ('Line2');
        l_address.city := l_address_json.get_string ('City');

        l_customer.firstname := p_customer_json.get_string ('FirstName');
        l_customer.lastname := p_customer_json.get_string ('LastName');
        l_customer.address := l_address;
        RETURN l_customer;
    END;
BEGIN
    l_json := customer_record_to_json (l_customer1);
    DBMS_OUTPUT.put_line ('Customer 1 (JSON): ' || l_json.stringify);
    l_record := customer_json_to_record (l_json);
    DBMS_OUTPUT.put_line ('Customer 1 (Record) (FirstName): ' || l_record.firstname);
    DBMS_OUTPUT.put_line ('Customer 1 (Record) (LastName): ' || l_record.lastname);
    DBMS_OUTPUT.put_line ('Customer 1 (Record) (Line1): ' || l_record.address.line1);
    DBMS_OUTPUT.put_line ('Customer 1 (Record) (Line2): ' || l_record.address.line2);
    DBMS_OUTPUT.put_line ('Customer 1 (Record) (City): ' || l_record.address.city);

    l_json := customer_record_to_json (l_customer2);
    DBMS_OUTPUT.put_line ('Customer 2 (JSON): ' || l_json.stringify);
    l_record := customer_json_to_record (l_json);
    DBMS_OUTPUT.put_line ('Customer 2 (Record) (FirstName): ' || l_record.firstname);
    DBMS_OUTPUT.put_line ('Customer 2 (Record) (LastName): ' || l_record.lastname);
    DBMS_OUTPUT.put_line ('Customer 2 (Record) (Line1): ' || l_record.address.line1);
    DBMS_OUTPUT.put_line ('Customer 2 (Record) (Line2): ' || l_record.address.line2);
    DBMS_OUTPUT.put_line ('Customer 2 (Record) (City): ' || l_record.address.city);
END;

Update

It is possible to just JSON_OBJECT(*) to create a JSON object, but the * expansion will not work with record types as you will get a ORA-40579: star expansion is not allowed.

If you do wish to use JSON_OBJECT to create the JSON instead of JSON_OBJECT_T, the types you are using will need to be predefined (not in the pl/sql block) and you will still need to define each field in the JSON structure. You will also need to define a table type of the RT_CUSTOMER type so that you can query from it.

CREATE TYPE RT_Address AS OBJECT (Line1 VARCHAR2 (100), Line2 VARCHAR2 (100), City VARCHAR2 (100));

CREATE TYPE RT_Customer AS OBJECT
(
    FirstName VARCHAR2 (100),
    LastName VARCHAR2 (100),
    Address RT_Address
);

CREATE TYPE rt_customer_t AS TABLE OF rt_customer;

SELECT json_object (
           'firstname' VALUE firstname,
           'lastname' VALUE lastname,
           'address' VALUE
               json_object ('line1' VALUE TREAT (address AS rt_address).line1,
                            'line2' VALUE TREAT (address AS rt_address).line2,
                            'city' VALUE TREAT (address AS rt_address).city)) as customer
  FROM TABLE (
           rt_customer_t (
               RT_Customer ('John',
                            'Doe',
                            rt_address ('123 Main Street', 'Apartment# 2A', 'London')),
               RT_Customer ('Jane',
                            'Smith',
                            rt_address ('456 Broken Dreams Blvd', NULL, 'Greenville'))));