Skip to main content
Adyant001
Silver ⭐️⭐️
Aug 12, 2024 5:15 AM UTC
Question

How to save single Json data to multiple Oracle table

Need to save Json data to multiple child tables. How should i do?

1 reply

florence0239
Gold ⭐️
Aug 13, 2024 6:00 AM UTC

@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

Translate