@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
