4

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!

Kyujiin
  • 99
  • 2
  • 10
  • There's a simple example how to work with similar data at https://dbfiddle.uk/?rdbms=oracle_18&fiddle=d7bc3fbb9456284d367cfe42673900dd but without more details on what you actually need it's hard to answer in a more specific way for your data. – Joachim Isaksson Aug 19 '21 at 05:05
  • What version of Oracle are you using? – Del Aug 19 '21 at 05:30
  • Does this answer your question? [How to select specific element from a JSON array in Oracles JSON CLOB type](https://stackoverflow.com/questions/39283363/how-to-select-specific-element-from-a-json-array-in-oracles-json-clob-type) – astentx Aug 19 '21 at 22:08

2 Answers2

2

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)

Del
  • 1,529
  • 1
  • 9
  • 18
0

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.

TineO
  • 1,023
  • 8
  • 24