Oracle APEX JSON data into Database - Is my JSON data the issue or can I fix it through PLSQL code?

Jasper Tanglib

Hi,

In Oracle APEX 20.2, I have a process which uses REST Services to read and insert JSON data into tables.

I just learned how to read JSON data and how to convert that into tables and columns for a database.

I have a sample JSON data, please refer below:

{

"things":[

{

"alphaNumber":"C92384"

,"trueName":"Oracle Names on Important Things"

,"anotherName":"Cloud Name for Oracle"

,"nameCategory":"A Category for Some Name"

,"immediateForeignCode":[

{

"immediateCode":"XXL",

"aNumber":[

{

"categorization":"SEND_HELP"

,"numberOrValue":911.1

}

]

},

{

"immediateCode":"ABC",

"aNumber":[

{

"categorization":"SEND_HELP"

,"numberOrValue":616.22

}

]

},

{

"immediateCode":"XYZ",

"aNumber":[

{

"categorization":"SEND_HELP"

,"numberOrValue":0.003142

}

]

}

]

"alphaNumber":"Z09823"

,"trueName":"Please Send Halp"

,"anotherName":"PLSQL and Oracle APEX"

,"nameCategory":"Something About Oracle"

,"immediateForeignCode":[

{

"immediateCode":"PLS",

"aNumber":[

{

"categorization":"SEND_HELP"

,"numberOrValue":8.1

}

]

},

{

"immediateCode":"HLP",

"aNumber":[

{

"categorization":"SEND_HELP"

,"numberOrValue":12.3

}

]

},

{

"immediateCode":"MIE",

"aNumber":[

{

"categorization":"SEND_HELP"

,"numberOrValue":903.123

}

]

}

]

}

]

}

In reading JSON data, every start of an opening bracket [ means it's a table and between its curly braces {} would be its columns and data of that table as separated by a colon : .

Based on the sample JSON above, the database would be the following:

Table THINGS, will have columns alphaNumber, trueName, anotherName, and nameCategory.

Table THINGS, will have a sub table called immediateForeignCode with a column immediateCode.

Table immediateForeignCode will have a sub table called aNumber with columns categorization and numberOrValue.

This pattern is repeated twice, so this is like 2 rows to be inserted in those tables.

The question is, how will I establish the data relationship between tables aNumber and immediateForeignCode as well as the relationship between tables immediateForeignCode and THINGS?

There is no ID column in the data that I can use to match a relationship between the data and tables.

Is the provided data the issue here or is there a way to make these relationships through PLSQL?

Any ideas or suggestions are appreciated!

-Jazz