Oracle 12c R2中JSON_TABLE嵌套路径条件提取GTIN的问题

假设你的业务表（比如命名为order_data）包含存储JSON的字段json_payload，典型JSON结构如下：

{
  "Packages": [
    {
      "PackageId": "SHP-001",
      "PackageWeight": 2.5,
      "PackageIdentifiers": [
        {"ExternalPackageId": "9876543210987", "ExternalPackageIdType": "GTIN"},
        {"ExternalPackageId": "S-PKG-001", "ExternalPackageIdType": "INTERNAL_ID"}
      ]
    },
    {
      "PackageId": "SHP-002",
      "PackageWeight": 1.2,
      "PackageIdentifiers": [
        {"ExternalPackageId": "S-PKG-002", "ExternalPackageIdType": "INTERNAL_ID"}
      ]
    }
  ]
}

要实现展平Packages节点+仅提取GTIN类型的ExternalPackageId+无GTIN时仍返回Package基础数据的需求，可使用带OUTER NESTED PATH和条件过滤的JSON_TABLE查询，具体SQL如下：

SELECT
  pkg.package_id,
  pkg.package_weight,
  pkg.gtin_code
FROM
  order_data t,
  JSON_TABLE(
    t.json_payload,
    '$.Packages[*]'
    COLUMNS (
      package_id VARCHAR2(50) PATH '$.PackageId',
      package_weight NUMBER(5,2) PATH '$.PackageWeight',
      -- 用OUTER关键字确保无GTIN时不丢失Package记录
      OUTER NESTED PATH '$.PackageIdentifiers[*]'
        COLUMNS (
          gtin_code VARCHAR2(14) PATH '$.ExternalPackageId'
        )
        -- 过滤仅GTIN类型的标识符
        WHERE '$.ExternalPackageIdType' = 'GTIN'
    )
  ) pkg;

关键说明： #

1. OUTER NESTED PATH的作用：替代普通NESTED PATH，当Package下没有匹配GTIN的标识符时，依然会返回该Package的基础数据，对应的gtin_code字段为NULL，满足"无GTIN记录也要返回Packages节点数据"的要求。
2. WHERE子句的位置：直接在NESTED PATH块内添加条件，精准过滤ExternalPackageIdType为"GTIN"的子节点，避免了后续额外过滤导致的父记录丢失。
3. 多GTIN场景处理：如果单个Package下存在多个GTIN类型的标识符，上述查询会返回多行（每个GTIN对应一行）。若需将多个GTIN合并为一行，可结合LISTAGG函数：

SELECT
  pkg.package_id,
  pkg.package_weight,
  NVL(LISTAGG(pkg.gtin_code, ', ') WITHIN GROUP (ORDER BY pkg.gtin_code), '无GTIN') AS gtin_codes
FROM
  order_data t,
  JSON_TABLE(
    t.json_payload,
    '$.Packages[*]'
    COLUMNS (
      package_id VARCHAR2(50) PATH '$.PackageId',
      package_weight NUMBER(5,2) PATH '$.PackageWeight',
      OUTER NESTED PATH '$.PackageIdentifiers[*]'
        COLUMNS (
          gtin_code VARCHAR2(14) PATH '$.ExternalPackageId'
        )
        WHERE '$.ExternalPackageIdType' = 'GTIN'
    )
  ) pkg
GROUP BY pkg.package_id, pkg.package_weight;

常见报错原因及解决： #

• 若之前在NESTED PATH加条件时报错，大概率是未使用OUTER关键字导致无匹配GTIN的Package被直接过滤，或条件语法错误（比如字符串未用单引号包裹），按照上述写法调整即可。

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