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.

Answer 1

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

Check Demo Here