Discussions
Categories
- 196.9K All Categories
- 2.2K Data
- 239 Big Data Appliance
- 1.9K Data Science
- 450.3K Databases
- 221.7K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 545 SQLcl
- 4K SQL Developer Data Modeler
- 187K SQL & PL/SQL
- 21.3K SQL Developer
- 295.9K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.6K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 155 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 18 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 440 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 Portuguese
Oracle APEX JSON data into Database - Is my JSON data the issue or can I fix it through PLSQL code?

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