How to convert a JSON array into a set of rows in Oracle?

Question

I want to convert a json array into a set of rows in Oracle.

Input:

'["a","b","c"]'

Output:

col_name
---------
a
b
c

PostgreSQL provides the function, jsonb_array_elements(jsonb) to do this.

How can I do this in Oracle 18c. Thank you.

Arun Palanisamy · Accepted Answer · 2020-04-27 05:45:16Z

6

You can use JSON_TABLE

SELECT value
FROM json_table( (select js from samp) , '$[*]'
                COLUMNS (value PATH '$')
               )

Replace js with your columnname & samp with your tablename in the above query

answered Apr 27, 2020 at 5:45

Arun Palanisamy

5,4898 gold badges34 silver badges57 bronze badges

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

1 Answer 1