Options
- Subscribe to RSS Feed
- Mark Question as New
- Mark Question as Read
- Float this Question for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
How to save single Json data to multiple Oracle table
Labels:
- Labels:
-
Apache NiFi
Explorer
Created ‎08-11-2024 10:15 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Need to save Json data to multiple child tables. How should i do?
1 REPLY 1
Explorer
Created ‎08-12-2024 11:00 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@Adyant001 wrote:Need to save Json data to multiple child tables. How should i do? choice advantage login
Hello,
To save JSON data to multiple Oracle tables, use the JSON_TABLE function to parse the JSON and then insert the parsed data into the respective tables. Here’s a concise example:
Parse JSON Data
SELECT *
FROM JSON_TABLE(
'{"employee": {"id": 1, "name": "John Doe", "department": "Sales", "address": {"street": "123 Main St", "city": "Anytown", "state": "CA"}}}',
'$.employee'
COLUMNS (
id NUMBER PATH '$.id',
name VARCHAR2(50) PATH '$.name',
department VARCHAR2(50) PATH '$.department',
street VARCHAR2(50) PATH '$.address.street',
city VARCHAR2(50) PATH '$.address.city',
state VARCHAR2(2) PATH '$.address.state'
)
) jt;
Insert Data into Tables
-- Insert into employee table
INSERT INTO employee (id, name, department)
SELECT id, name, department
FROM JSON_TABLE(
'{"employee": {"id": 1, "name": "John Doe", "department": "Sales", "address": {"street": "123 Main St", "city": "Anytown", "state": "CA"}}}',
'$.employee'
COLUMNS (
id NUMBER PATH '$.id',
name VARCHAR2(50) PATH '$.name',
department VARCHAR2(50) PATH '$.department'
)
);
-- Insert into address table
INSERT INTO address (employee_id, street, city, state)
SELECT id, street, city, state
FROM JSON_TABLE(
'{"employee": {"id": 1, "name": "John Doe", "department": "Sales", "address": {"street": "123 Main St", "city": "Anytown", "state": "CA"}}}',
'$.employee'
COLUMNS (
id NUMBER PATH '$.id',
street VARCHAR2(50) PATH '$.address.street',
city VARCHAR2(50) PATH '$.address.city',
state VARCHAR2(2) PATH '$.address.state'
)
);
This should help you get started!
Best regards,
florence0239
