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