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!
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)
See if this can help.
PLSQL looping through JSON object
It answers more or less what you are asking, although I'm not sure if it can handle not knowing the column names, figuring them out and creating a table from them.
Otherwise you could probably do some REGEXP parsing to figure out the distinct column names first, then either go through it with the json package, or just loop through it manually.