JSON array from a CLOB column

Question

Im having trouble trying to retrieve data from a JSON that is stored in a CLOB column.

The part im trying to retrive is:

"multiplaResposta":["Cardiologista","Endocrinologista","Neurologista","Pneumologista"]

The closed I managed is this:

SELECT jt.*
FROM FICHAS_AVALIACAO_HEALTHCHESS fa
     CROSS APPLY JSON_TABLE(
         fa.json_data,
         '$[*].respostas[*]' 
         COLUMNS (
             pergunta VARCHAR2(200)              PATH '$.pergunta',
             resposta VARCHAR2(4000)             PATH '$.resposta',
             multiplaResposta1 CLOB FORMAT JSON  PATH '$.multiplaResposta',
             multiplaResposta2 CLOB              PATH '$.multiplaResposta'
         )
     ) jt
       OUTER APPLY JSON_TABLE(
         COALESCE(jt.multiplaResposta1, '["' || jt.multiplaResposta2 || '"]'),
         '$[*]'
         COLUMNS(
           multiplaResposta CLOB PATH '$'
         )
       ) m    
WHERE fa.cpf = '213.029.030-20';

But that only gives me another column indicating that the output is a CLOB information. What i need is something like this on the output:

multiplaResposta
Cardiologista, Endocrinologista, Neurologista, Pneumologista

Answer 1

... trying to retrieve data from a JSON that is stored in a CLOB column

If your clob column containes json data like here:

Create Table
  tbl AS
     Select 1 "ID", 
            json_object('multiplaResposta' 
                         value json_array('"Cardiologista","Endocrinologista","Neurologista","Pneumologista"')
                         returning clob
                        ) "JSON_CLOB"
      From Dual;

ID	JSON_CLOB
1	{"multiplaResposta":[""Cardiologista","Endocrinologista","Neurologista","Pneumologista""]}

... then ...

--    S Q L :  
Select    t.ID, j.multiplaResposta
From      tbl t, 
          JSON_TABLE( t.JSON_CLOB,  
                     '$'
                      COLUMNS( multiplaResposta CLOB path '$.multiplaResposta[*]' )
                    ) j;

ID	MULTIPLARESPOSTA
1	"Cardiologista","Endocrinologista","Neurologista","Pneumologista"

fiddle

More about generating, parsing and using JSON data here

Answer 2

The solution I got, the same code I post on the question, but using LISTAGG and DBMS_LOB.SUBSTR, here:

LISTAGG(DBMS_LOB.SUBSTR(m.multiplaResposta, 4000, 1), ' | ') WITHIN GROUP (ORDER BY MULTIPLARESPOSTA) AS multiplaResposta

Thanks everyone!