如何提取JSON键并将数据存储到Oracle指定表结构中？

1. 创建目标表 #

首先创建符合需求的表结构：

CREATE TABLE employment_records (
    job_type VARCHAR2(100),
    start_date DATE,
    employer_name VARCHAR2(200),
    employer_zip VARCHAR2(20)
);

2. 解析JSON并插入数据 #

利用Oracle的JSON_KEYS提取动态键（如Applicant1stJob），再通过JSON_TABLE拆分数据并提取字段值，最终插入目标表。

示例（JSON为字面量） #

如果JSON数据是直接提供的字面量，可使用以下SQL：

WITH json_data AS (
    SELECT '{
        "Employment":{
            "Applicant1stJob":{
                "StartDate":"2015-09-28",
                "Employer":{
                    "ZipCode":"123",
                    "Telephone":"11111111",
                    "Street":" 1st job street ",
                    "State":"NY",
                    "Name":"Employer 1",
                    "City":"New York"
                }
            },
            "Applicant2ndJob":{
                "StartDate":"2018-09-28",
                "Employer":{
                    "ZipCode":"345",
                    "Telephone":"222222222",
                    "Street":"1st job street",
                    "State":"NY",
                    "Name":"Employer 2",
                    "City":"New York"
                }
            }
        }' AS json_clob FROM dual
)
INSERT INTO employment_records (job_type, start_date, employer_name, employer_zip)
SELECT
    keys.job_type,
    TO_DATE(jt.start_date, 'YYYY-MM-DD') AS start_date,
    jt.employer_name,
    jt.employer_zip
FROM json_data,
     -- 提取Employment下的所有动态键
     JSON_TABLE(
         JSON_KEYS(json_clob, '$.Employment'),
         '$[*]' COLUMNS job_type VARCHAR2(100) PATH '$'
     ) keys,
     -- 根据每个键提取对应的数据字段
     JSON_TABLE(
         json_clob,
         '$.Employment.' || keys.job_type || ''
         COLUMNS
             start_date VARCHAR2(10) PATH '$.StartDate',
             employer_name VARCHAR2(200) PATH '$.Employer.Name',
             employer_zip VARCHAR2(20) PATH '$.Employer.ZipCode'
     ) jt;

示例（JSON存储在现有表中） #

如果JSON数据已经存储在某个表（如source_table）的json_column字段中，使用以下SQL：

INSERT INTO employment_records (job_type, start_date, employer_name, employer_zip)
SELECT
    keys.job_type,
    TO_DATE(jt.start_date, 'YYYY-MM-DD') AS start_date,
    jt.employer_name,
    jt.employer_zip
FROM source_table st,
     JSON_TABLE(
         JSON_KEYS(st.json_column, '$.Employment'),
         '$[*]' COLUMNS job_type VARCHAR2(100) PATH '$'
     ) keys,
     JSON_TABLE(
         st.json_column,
         '$.Employment.' || keys.job_type || ''
         COLUMNS
             start_date VARCHAR2(10) PATH '$.StartDate',
             employer_name VARCHAR2(200) PATH '$.Employer.Name',
             employer_zip VARCHAR2(20) PATH '$.Employer.ZipCode'
     ) jt;

注意事项 #

• 需使用Oracle 12c及以上版本，该版本开始支持JSON_TABLE和JSON_KEYS函数
• 确保JSON中的日期格式与TO_DATE函数指定的格式一致（示例中为YYYY-MM-DD）
• 如果JSON字段为VARCHAR2类型，可直接替换示例中的json_clob使用

内容的提问来源于stack exchange，提问作者kumar