Oracle transfer parent child tree to json



---TREE transfor to JASON
 WITH connect_by_query as ( 
   select ROWNUM as rnum
 ,  ORG_ENTITY_ID
    ,ORG_ENTITY_NAME
    ,PARENT_ORG_ENTITY_ID,HIRARCHY_LEVEL as Lvl
from MS_FLOWDOWN_TMP
start with ORG_ENTITY_ID='*' connect by nocycle prior ORG_ENTITY_ID=PARENT_ORG_ENTITY_ID
)
select
  CASE
    /* the top dog gets a left curly brace to start things off */
    WHEN Lvl = 1 THEN '{'
    /* when the last level is lower (shallower) than the current level, start a "children" array */
    WHEN Lvl - LAG(Lvl) OVER (order by rnum) = 1 THEN ',"isFolder": true,"children" : [{'
    ELSE ',{'
  END
  || ' "title" : "' || ORG_ENTITY_NAME || '" '
  ||',"key":"'||ORG_ENTITY_ID|| '" '
 
 
  /* when the next level lower (shallower) than the current level, close a "children" array */
  || CASE WHEN LEAD(Lvl, 1, 1) OVER (order by rnum) - Lvl <= 0
     THEN '}' || rpad( ' ', 1+ (-2 * (LEAD(Lvl, 1, 1) OVER (order by rnum) - Lvl)), ']}' )
     ELSE NULL
  END as JSON_SNIPPET
from connect_by_query
;