如何用Oracle JSON_TABLE解析含嵌套JSON的CLOB并插入多行

假设你的p_clob中JSON结构核心包含receivablesInvoiceLines数组，示例结构如下：

{
  "receivablesInvoiceLines": [
    {"lineId": 1, "lineAmount": 150.00, "lineDesc": "办公耗材"},
    {"lineId": 2, "lineAmount": 300.50, "lineDesc": "服务器租赁"},
    {"lineId": 3, "lineAmount": 80.00, "lineDesc": "软件授权"},
    {"lineId": 4, "lineAmount": 220.00, "lineDesc": "运维服务"}
  ]
}

你可以通过JSON_TABLE遍历数组元素，生成对应行数据插入dump_Data表，具体SQL语句如下：

INSERT INTO dump_Data (line_id, line_amount, line_description)
SELECT jt.line_id, jt.line_amount, jt.line_description
FROM JSON_TABLE(
    p_clob,
    '$.receivablesInvoiceLines[*]' COLUMNS (
        line_id NUMBER PATH '$.lineId',
        line_amount NUMBER PATH '$.lineAmount',
        line_description VARCHAR2(200) PATH '$.lineDesc'
    )
) jt;

关键细节说明： #

• '$.receivablesInvoiceLines[*]'：路径表达式定位到根节点下的receivablesInvoiceLines数组，[*]表示遍历数组所有元素，每个元素对应生成一行记录
• COLUMNS子句：定义JSON字段与目标表字段的映射关系
  • 每个列需指定数据类型和JSON路径（PATH），路径要准确匹配JSON元素的键名
  • 若JSON字段名与目标表字段名完全一致，可省略PATH子句，例如line_id NUMBER会自动匹配JSON中的line_id键
• 提前验证JSON合法性：可用JSON_VALID(p_clob)函数检查CLOB中的JSON格式是否有效，避免解析报错

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