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

This question is similar to: How to convert CLOB to VARCHAR2 inside oracle pl/sql. If you believe it’s different, please edit the question, make it clear how it’s different and/or how the answers on that question are not helpful for your problem. — Luuk
– Luuk, Commented Jan 14 at 14:19
You have only shown a small part of one JSON value, but your code seems to work. If your client is just showing 'CLOB' or '(CLOB)' then you need to change the client's settings to show those values in full - how will depend on which client - or convert to VARCHAR2 (maybe via substr()). — Alex Poole
– Alex Poole, Commented Jan 14 at 14:28

Julio Cezar Teixeira · Accepted Answer · 2025-01-14 17:03:03Z

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!

answered Jan 14 at 17:03

Julio Cezar Teixeira

92 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Alex Poole Jan 14 at 18:05

If you're going to substr() the value from m.multiplaResposta anyway, and it will always be a short value, then you can declare it as say VARCHAR2(30) - with a suitable size - instead of CLOB, in the second columns clause. (You can also do string aggregation in XPath so could possibly avoid needing a second XMLTable at all, maybe...)

d r · Accepted Answer · 2025-01-14 17:18:48Z

... 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

Collectives™ on Stack Overflow

JSON array from a CLOB column

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related