How to save single Json data to multiple Oracle table

[#1]

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

[#2]

---

@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