Parse Multiple Json Array Elements in Oracle

Question

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

But i want to get the result like

9952044727,5464646464

What changes i will need to put in my query?

MT0 · Accepted Answer · 2020-10-26 12:44:11Z

2

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

answered Oct 26, 2020 at 12:44

MT0

173k12 gold badges70 silver badges136 bronze badges

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

Collectives™ on Stack Overflow

Parse Multiple Json Array Elements in Oracle

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related