create table MY_PARENT_TABLE ( 
    PK int,  
    TNAME varchar2(20 char),  
    DEPT char(3 char) 
    )

create table MY_CHILD_TABLE ( 
    PK int,  
    SIBLING_ROW int,  
    CC int,  
    XX int 
    )

insert into MY_PARENT_TABLE  
select 1, 'Parent Row 1', 'AAA' from dual 
union 
select 2, 'Parent Row 2', 'BBB' from dual

insert into MY_CHILD_TABLE  
select 1, 1, 20, 22 from dual 
union 
select 1, 2, 30, 33 from dual 
union 
select 1, 3, 40, 44 from dual

commit

select 1 as X, json_object( 
    'pk' value p.PK,  
    'tName' value p.TNAME,  
    'dept' value p.DEPT,  
    'child' value json_arrayagg( 
        json_object( 
            'CC' value c.CC,  
            'XX' value c.XX 
            ) 
        ) 
    ) as J 
from MY_PARENT_TABLE p 
left join MY_CHILD_TABLE c  
    on c.PK = p.PK 
group by p.PK, p.TNAME, p.DEPT

select 2 as X, json_object( 
    'pk' value p.PK,  
    'tName' value p.TNAME,  
    'dept' value p.DEPT,  
    'child' value c.CHILD 
    ) as J 
from MY_PARENT_TABLE p 
left join ( 
    select PK,  
        json_arrayagg( 
            json_object( 
                'CC' value CC,  
                'XX' value XX 
                ) 
            ) as CHILD 
    from MY_CHILD_TABLE 
    group by PK 
    ) c  
    on c.PK = p.PK

select 3 as X, json_object( 
    'pk' value p.PK,  
    'tName' value p.TNAME,  
    'dept' value p.DEPT,  
    'child' value json_arrayagg( 
        case when c.PK is null then '[]' -- 
        else  
            json_object( 
                'CC' value c.CC,  
                'XX' value c.XX 
                ) 
        end  
            ) 
    ) as J 
from MY_PARENT_TABLE p 
left join MY_CHILD_TABLE c  
    on c.PK = p.PK 
group by p.PK, p.TNAME, p.DEPT

select 4 as X, json_object( 
    'pk' value p.PK,  
    'tName' value p.TNAME,  
    'dept' value p.DEPT,  
    'child' value coalesce(c.CHILD, '[]') 
    ) as J 
from MY_PARENT_TABLE p 
left join ( 
    select PK,  
        json_arrayagg( 
            json_object( 
                'CC' value CC,  
                'XX' value XX 
                ) 
            ) as CHILD 
    from MY_CHILD_TABLE 
    group by PK 
    ) c  
    on c.PK = p.PK

select 5 as X, replace(json_object( 
    'pk' value p.PK,  
    'tName' value p.TNAME,  
    'dept' value p.DEPT,  
    'child' value c.CHILD 
    ), ',"child":null}', ',"child":[]}') as J 
from MY_PARENT_TABLE p 
left join ( 
    select PK,  
        json_arrayagg( 
            json_object( 
                'CC' value CC,  
                'XX' value XX 
                ) 
            ) as CHILD 
    from MY_CHILD_TABLE 
    group by PK 
    ) c  
    on c.PK = p.PK

drop table MY_PARENT_TABLE

drop table MY_CHILD_TABLE