Extract all json value in comm saparated string in oracle-CodePudding

I have following Array :

 "Sorted Reason Code Table": [
                  "PCC018",
                  "PCC020",
                  "PCC021",
                  "PCC023",
                  "PCC025",
                  "PCC031",
                  "PCC059",
                  "PCC061",
                  "",
                  "",
                  "",
                  "",
                  "",
                  "",
                  "",
                  "",
                  "",
                  "",
                  "",
                  ""
                ]

I want to get all values in string. Output should be :

PCC010,PCC012,PCC0034 etc.

All values are dynamic some time there will be value and some time it would be blank .

Any help would be appreciated .

CodePudding user response：

You can use:

SELECT LISTAGG(reason_code, ',') WITHIN GROUP (ORDER BY ROWNUM) AS reason_codes
FROM   table_name t
       CROSS APPLY JSON_TABLE(
         t.value,
         '$."Sorted Reason Code Table"[*]'
         COLUMNS (
           reason_code VARCHAR2(10) PATH '$'
         )
       ) j
WHERE  reason_code IS NOT NULL
GROUP BY t.ROWID

Which, for the sample data:

CREATE TABLE table_name (value BLOB CHECK (value IS JSON));

INSERT INTO table_name (value)
VALUES ('{"Sorted Reason Code Table": ["PCC018","PCC020","PCC021","PCC023","PCC025","PCC031","PCC059","PCC061","","","","","","","","","","",""]}');

Outputs:

REASON_CODES

PCC018,PCC020,PCC021,PCC023,PCC025,PCC031,PCC059,PCC061

REASON_CODES
PCC018,PCC020,PCC021,PCC023,PCC025,PCC031,PCC059,PCC061

db<>fiddle here

CodePudding user response：

You could do something like this (abusing the JSON_ARRAYAGG function by telling it that the reason codes are in format JSON, when in fact they aren't). Using the data from MT0's answer.

select substr(lst, 2, length(lst) - 2) as reason_codes
from   (
         select json_arrayagg(reason_code format json
                              order by reason_code) as lst
         from   table_name t cross apply
                json_table(t.value, '$."Sorted Reason Code Table"[*]'
                           columns (reason_code varchar2(10) path '$')
                          )
       )
;




REASON_CODES                                              
----------------------------------------------------------
PCC018,PCC020,PCC021,PCC023,PCC025,PCC031,PCC059,PCC061