How to save single Json data to multiple Oracle table

Adyant001 — Mon, 12 Aug 2024 05:15:22 GMT

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

Re: How to save single Json data to multiple Oracle table

florence0239 — Tue, 13 Aug 2024 06:00:35 GMT

@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

question How to save single Json data to multiple Oracle table in Support Questions

How to save single Json data to multiple Oracle table

Re: How to save single Json data to multiple Oracle table