Yeah, but then you'd still need to know the type attributes. I tested and this solution works - http://www.armbruster-it.org/index.php/12-it/pl-sql/12-oracle-xml-and-json-goodies I thought maybe there's something similar already built into the Oracle.

create or replace type user_type as object (
  attr1     varchar2(100),
  attr2     varchar2(100),
  add_extra varchar2(100),
  attrs     varchar2(100)
);
/

select json_objectagg (
         att value val
       )
from   xmltable (
  '/USER_TYPE/*'
  passing xmltype ( user_type ( 'val1', 'val2', 'new', 'newer' ) )
  columns 
    att varchar2(100) path './name()',
    val varchar2(100) path './text()'
);

JSON_OBJECTAGG(ATTVALUEVAL)                                         
{"ATTR1":"val1","ATTR2":"val2","ADD_EXTRA":"new","ATTRS":"newer"} 

Team,

As mentioned in the above blog, do we still need to convert data to XML and then to JSON structure, rather than using the JSON generation function like this?

Kindly advice.

demo@ORA12C> select * from ex01_person_tb;

        ID NAME
---------- --------------------
         1 Roger Waters
         2 David Gilmour

demo@ORA12C> select * from ex01_phone_tb;

        ID  PERSON_ID PHONE_NUMBER
---------- ---------- --------------------------------------------------
         1          1 543 454433
         2          1 512 4776443
         3          1 521 6454423
         4          2 212 8332464
         5          2 312 6736423

demo@ORA12C> select json_query( json_object( 'ROWSET' value json_arrayagg( json_object( 'id' value t1.id ,
  2    'name' value t1.name,
  3    'phone_numbers' value t2.txt )  ) ) ,'$' pretty )
  4  from ex01_person_tb t1, (  select person_id,
  5            json_arrayagg ( json_object('id' value id ,
  6                  'phone_number' value phone_number) ) txt
  7          from ex01_phone_tb
  8          group by person_id ) t2
  9  where t1.id = t2.person_id
 10  /

JSON_QUERY(JSON_OBJECT('ROWSET'VALUEJSON_ARRAYAGG(JSON_OBJECT('ID'VALUET1.ID,'NAME'VALUET1.NAME,'PHONE_NUMBERS'VALUET2.TXT))),'$'PR
-----------------------------------------------------------------------------------------------------------------------------------
{
  "ROWSET" :
  [
    {
      "id" : 1,
      "name" : "Roger Waters",
      "phone_numbers" :
      [
        {
          "id" : 1,
          "phone_number" : "543 454433"
        },
        {
          "id" : 3,
          "phone_number" : "521 6454423"
        },
        {
          "id" : 2,
          "phone_number" : "512 4776443"
        }
      ]
    },
    {
      "id" : 2,
      "name" : "David Gilmour",
      "phone_numbers" :
      [
        {
          "id" : 4,
          "phone_number" : "212 8332464"
        },
        {
          "id" : 5,
          "phone_number" : "312 6736423"
        }
      ]
    }
  ]
}


demo@ORA12C>

Sorry, I wasn't clear enough. Here's the XMLTYPE demo. Would be nice to have the same functionality with JSON.


CREATE OR REPLACE TYPE tt_address AS OBJECT
(
address VARCHAR2(100)
)
/

CREATE OR REPLACE TYPE tt_addresses_arr_typ AS TABLE OF tt_address
/

CREATE OR REPLACE TYPE tt_person_base AS OBJECT
(
NAME VARCHAR2(100)
)
NOT FINAL
/

CREATE OR REPLACE TYPE tt_person UNDER tt_person_base
(
addresses tt_addresses_arr_typ
)
/

CREATE OR REPLACE TYPE tt_person1 UNDER tt_person_base
(
name2 varchar2(100)
)
/


-- then I can do this
SELECT CURSOR (SELECT tt_person(NAME => 'name'
,addresses => tt_addresses_arr_typ(tt_address(address => 'address1')
,tt_address(address => 'address2')))
FROM dual)
FROM dual
/

-- and convert it to XML
SELECT xmltype(CURSOR (SELECT tt_person(NAME => 'name'
,addresses => tt_addresses_arr_typ(tt_address(address => 'address1')
,tt_address(address => 'address2')))
FROM dual))
FROM dual /

-- or like this
DECLARE
l_person tt_person;
l_xml xmltype;
BEGIN
l_person := tt_person(NAME => 'name'
,addresses => tt_addresses_arr_typ(tt_address(address => 'address1')
,tt_address(address => 'address2')));
l_xml := xmltype(l_person);
dbms_output.put_line(l_xml.getclobval);
END;
/


-- or by using EXECUTE IMMEDIATE
DECLARE
l_person tt_person;
l_xml xmltype;
BEGIN
l_person := tt_person(NAME => 'name'
,addresses => tt_addresses_arr_typ(tt_address(address => 'address1')
,tt_address(address => 'address2')));
EXECUTE IMMEDIATE 'begin :l_xml := xmltype(:l_person); end;'
USING OUT l_xml, IN l_person;
dbms_output.put_line(l_xml.getclobval);
END;
/


-- and finally like this, so that it works with any tt_person_base object
-- this is what's currently missing in Oracle's JSON implementation
DECLARE
l_person tt_person;
l_person1 tt_person1;

FUNCTION to_xml(pi_type IN tt_person_base) RETURN xmltype IS
l_xml xmltype;
BEGIN
EXECUTE IMMEDIATE 'begin :xml := xmltype(:pi_type); end;'
USING OUT l_xml, IN pi_type;
RETURN l_xml;
END to_xml;

BEGIN
l_person := tt_person(NAME => 'name'
,addresses => tt_addresses_arr_typ(tt_address(address => 'address1')
,tt_address(address => 'address2')));
dbms_output.put_line(to_xml(l_person).getclobval);

l_person1 := tt_person1(NAME => 'name', name2 => 'name2');
dbms_output.put_line(to_xml(l_person1).getclobval);
END;
/

Oh, there's no need to use execute immediate :)

-- and finally like this, so that it works with any tt_person_base object
-- this is what's currently missing in Oracle's JSON implementation
DECLARE
l_person tt_person;
l_person1 tt_person1;

FUNCTION to_xml(pi_type IN tt_person_base) RETURN xmltype IS
l_xml xmltype;
BEGIN
l_xml := xmltype(pI_type);
RETURN l_xml;
END to_xml;

BEGIN
l_person := tt_person(NAME => 'name'
,addresses => tt_addresses_arr_typ(tt_address(address => 'address1')
,tt_address(address => 'address2')));
dbms_output.put_line(to_xml(l_person).getclobval);

l_person1 := tt_person1(NAME => 'name', name2 => 'name2');
dbms_output.put_line(to_xml(l_person1).getclobval);
END;
/

Been making use of the APEX_JSON.WRITE for a while, works for me (Still involves converting type->XML->JSON)

FUNCTION get_mytype_json (
l_mytype mytype)
RETURN CLOB
AS
l_xml XMLTYPE;
l_json_output CLOB;
BEGIN
l_xml := xmltype.createXML (l_mytype);

APEX_JSON.initialize_clob_output;
APEX_JSON.write (l_xml);
l_json_output := APEX_JSON.get_clob_output;

APEX_JSON.free_output;

RETURN l_json_output;
END get_mytype_json ;

create or replace type user_type as object (
  attr1 varchar2(100),
  attr2 varchar2(100)
);
/

with obj as (
  select user_type ( 'this', 'that' ) o from dual
)
  select json_object ( o ) from obj;

JSON_OBJECT(O)                    
{"ATTR1":"this","ATTR2":"that"}  
  
create or replace type user_type_tab
  is table of user_type;
/

with obj as (
  select user_type_tab (
    user_type ( 'this', 'that' ),
    user_type ( 'another this', 'another that' )
  ) o 
  from dual
)
  select json_object ( o ) from obj;

JSON_OBJECT(O)                                                                      
[{"ATTR1":"this","ATTR2":"that"},{"ATTR1":"another this","ATTR2":"another that"}]