CODEWITHSUNDEEP
sqlarraysjsonoracle-database
deltaforce
deltaforce

Reputation: 85

Parse Multiple Json Array Elements in Oracle

I have to parse below json in oracle that contain array element as showing below

{"orgPhoneNum":[["9952044727"],["5464646464"]]}

I tried below statement

SELECT JSON_QUERY('{"orgPhoneNum":[["9952044727"],["5464646464"]]}', '$.orgPhoneNum') 
  FROM DUAL;

that return the result like

enter image description here

But i want to get the result like

9952044727,5464646464

What changes i will need to put in my query?

Upvotes: 1

Views: 783

Answers (1)

MT0
MT0

Reputation: 167774

You can get the array values using JSON_TABLE and then aggregate using LISTAGG:

SELECT LISTAGG( orgPhoneNum, ',' ) WITHIN GROUP ( ORDER BY row_number )
         AS orgPhoneNums
FROM   JSON_TABLE(
         '{"orgPhoneNum":[["9952044727"],["5464646464"]]}',
         '$.orgPhoneNum[*][*]'
         COLUMNS (
           row_number FOR ORDINALITY,
           orgPhoneNum VARCHAR2(10) PATH '$'
         )
       )

Which outputs:

| ORGPHONENUMS          |
| :-------------------- |
| 9952044727,5464646464 |

db<>fiddle here

Upvotes: 2

Related Questions