如何用Oracle JSON_TABLE解析GET响应指定字符串并存储为CLOB

给定如下GET请求的JSON响应：

{
  "PrepaymentIncludedonInvoiceFlag": null,
  "ReferenceKeyTwo": null,
  "ProductTable": null,
  "links": [
    {
      "rel": "self",
      "href": "domain.com/fscmRestApi/resources/11.13.18.05/invoices/300000165112384/child/invoiceLines/00020000000EACED00057708000110D93B462A400000000AACED0005770400000001",
      "name": "invoiceLines",
      "kind": "item",
      "properties": {
        "changeIndicator": "ACED0005737200136A6176612E7574696C2E41727261794C6973747881D21D99C7619D03000149000473697A65787000000001770400000001737200116A6176612E6C616E672E496E746567657212E2A0A4F781873802000149000576616C7565787200106A6176612E6C616E672E4E756D62657286AC951D0B94E08B02000078700000000278"
      }
    }
  ]
}

需要从links数组中rel为self的对象的href字段路径里，提取最后一段高亮的字符串，并将其存储到CLOB类型字段中。请使用Oracle的JSON_TABLE或其他专属方法实现该需求。

方法1：使用JSON_TABLE结合正则表达式 #

通过JSON_TABLE解析JSON数组并筛选目标条目，再用正则表达式提取URL最后一段：

WITH json_data AS (
  SELECT '{
    "PrepaymentIncludedonInvoiceFlag": null,
    "ReferenceKeyTwo": null,
    "ProductTable": null,
    "links": [
      {
        "rel": "self",
        "href": "domain.com/fscmRestApi/resources/11.13.18.05/invoices/300000165112384/child/invoiceLines/00020000000EACED00057708000110D93B462A400000000AACED0005770400000001",
        "name": "invoiceLines",
        "kind": "item",
        "properties": {
          "changeIndicator": "ACED0005737200136A6176612E7574696C2E41727261794C6973747881D21D99C7619D03000149000473697A65787000000001770400000001737200116A6176612E6C616E672E496E746567657212E2A0A4F781873802000149000576616C7565787200106A6176612E6C616E672E4E756D62657286AC951D0B94E08B02000078700000000278"
        }
      }
    ]
  }' AS json_clob FROM dual
)
SELECT TO_CLOB(REGEXP_SUBSTR(jt.href, '[^/]+$')) AS extracted_id
FROM json_data jd,
     JSON_TABLE(jd.json_clob, '$.links[*]'
                COLUMNS
                  rel VARCHAR2(20) PATH '$.rel',
                  href VARCHAR2(1000) PATH '$.href'
               ) jt
WHERE jt.rel = 'self';

说明： #

• JSON_TABLE将links数组展开为行，提取rel和href字段
• REGEXP_SUBSTR(jt.href, '[^/]+$')匹配URL最后一个/之后的所有字符
• TO_CLOB()将提取结果转换为CLOB类型，满足存储要求

方法2：使用JSON_VALUE配合字符串截取 #

如果仅需单个匹配项，可直接用JSON_VALUE定位目标href，再通过字符串函数截取：

WITH json_data AS (
  SELECT '{
    "PrepaymentIncludedonInvoiceFlag": null,
    "ReferenceKeyTwo": null,
    "ProductTable": null,
    "links": [
      {
        "rel": "self",
        "href": "domain.com/fscmRestApi/resources/11.13.18.05/invoices/300000165112384/child/invoiceLines/00020000000EACED00057708000110D93B462A400000000AACED0005770400000001",
        "name": "invoiceLines",
        "kind": "item",
        "properties": {
          "changeIndicator": "ACED0005737200136A6176612E7574696C2E41727261794C6973747881D21D99C7619D03000149000473697A65787000000001770400000001737200116A6176612E6C616E672E496E746567657212E2A0A4F781873802000149000576616C7565787200106A6176612E6C616E672E4E756D62657286AC951D0B94E08B02000078700000000278"
        }
      }
    ]
  }' AS json_clob FROM dual
)
SELECT TO_CLOB(
         SUBSTR(
           JSON_VALUE(json_clob, '$.links[?(@.rel=="self")].href'),
           INSTR(JSON_VALUE(json_clob, '$.links[?(@.rel=="self")].href'), '/', -1) + 1
         )
       ) AS extracted_id
FROM json_data;

说明： #

• JSON_VALUE(json_clob, '$.links[?(@.rel=="self")].href')通过JSON路径直接定位目标href
• INSTR(..., '/', -1)找到最后一个/的位置，SUBSTR()从该位置后一位截取到末尾
• TO_CLOB()完成类型转换

存储到CLOB字段示例 #

若要将结果存入表的CLOB字段，可使用UPDATE或INSERT语句：

-- 假设存在表invoice_info，包含json_response CLOB和extracted_id CLOB字段
UPDATE invoice_info
SET extracted_id = (
  SELECT TO_CLOB(REGEXP_SUBSTR(jt.href, '[^/]+$'))
  FROM JSON_TABLE(json_response, '$.links[*]'
                  COLUMNS
                    rel VARCHAR2(20) PATH '$.rel',
                    href VARCHAR2(1000) PATH '$.href'
                 ) jt
  WHERE jt.rel = 'self'
)
WHERE invoice_id = 300000165112384; -- 添加实际筛选条件

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