PLSQL turn clob containing json into table

Question

Is there a way to turn a clob containing JSON Object into table

for example I have a clob containing [{"a":"1","b":"1"; "a":"2", "b":"2"; "a":"2","b":"2"}] I want to turn this into a table to join it with other tables in my database.

is there a way to do it?

Thank you!

Answer 1

Your JSON is definitely not well formatted. However, once that is cleaned up, you can use JSON_TABLE to achieve your goals:

WITH test_data (json) AS
(
  SELECT '{"rows":[{"a":"1","b":"1"},{"a":"2", "b":"2"},{"a":"2","b":"2"}]}' FROM DUAL
)
SELECT jt.*
FROM test_data td,
JSON_TABLE(td.json, 
           '$.rows[*]' 
           COLUMNS (row_number FOR ORDINALITY, 
                    a INTEGER PATH '$.a', 
                    b INTEGER PATH '$.b')) jt

Produces the following results:

row_number	a	b
1	1	1
2	2	2
3	2	2

Here is a DBFiddle showing how this works (Link)