with tree (id, name, parent_id, curr_level, children) as (  
  select id   
      , name  
      , null parent_id  
      , 1 curr_level  
      , children  
    from json_table('{"id": 1, "name": "galaxies", "children": [{"id": 2, "name": "milkyway", "children": [ {"id": 3, "name": "sun"}]}]}', '$'  
         columns (id number path '$.id'  
                , name varchar2(4000) path '$.name'  
                , children varchar2(32000) format json path '$.children'))  
   union all  
  select json_value(children, '$[0].id' returning number)  
       , json_value(children, '$[0].name' returning varchar2)  
       , id  
       , curr_level + 1  
       , json_query(children, '$[0].children' returning varchar2)  
    from tree  
   where json_value(children, '$[0].id' returning number) is not null)  
select id  
     , name  
     , parent_id  
     , curr_level  
  from tree