Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

JSON array from a CLOB column

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
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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!


  2. 0

    … 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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search