Oracle PL/SQL中JSON_TABLE提取PRODUCTS返回空数组问题

云原生阿岚

2026-6-14

Oracle PL/SQL JSON_TABLE提取嵌套数组返回空数组问题解决

在使用Oracle PL/SQL存储过程处理JSON输入时，通过JSON_TABLE提取嵌套的Products字段时，始终返回空数组[]，无法获取预期的产品列表。

原存储过程代码（泛化名）

CREATE OR REPLACE PACKAGE BODY "MY_PACKAGE" AS

    FUNCTION ProcessRequest(p_request_data IN CLOB, p_region_id IN VARCHAR2) RETURN CLOB IS
        v_result CLOB;
        v_geo    NUMBER;
        v_city   VARCHAR2(100);
    BEGIN
        SELECT json_value(p_request_data, '$.data.CITY')
        INTO v_city
        FROM dual;

        IF v_city = 'Town' THEN
            v_geo := 1;
        ELSE
            v_geo := 2;
        END IF;

        SELECT JSON_OBJECT(
                   'REQUEST_INFO' VALUE JSON_OBJECT(
                        'geo' VALUE v_geo,
                        'provider' VALUE p_region_id,
                        'nationalId' VALUE json_value(p_request_data, '$.data.NATIONAL_ID'),
                        'name' VALUE json_value(p_request_data, '$.data.FIRST_NAME'),
                        'family' VALUE json_value(p_request_data, '$.data.LAST_NAME'),
                        'phone' VALUE json_value(p_request_data, '$.data.PHONE'),
                        'requestNo' VALUE json_value(p_request_data, '$.data.REQUEST_NO'),
                        'subBranch' VALUE LPAD(json_value(p_request_data, '$.data.BRANCH_CODE'), 5, '0')
                    ),
                   'SUB_REQUEST' VALUE (
                       SELECT JSON_ARRAYAGG(
                                  JSON_OBJECT(
                                      'PHASE' VALUE PHASE,
                                      'LIC_STT_NAME' VALUE LIC_STT_NAME,
                                      'AMPER' VALUE TO_NUMBER(AMPER),
                                      'KILOWATT' VALUE TO_NUMBER(KILOWATT),
                                      'LIC_STT' VALUE LIC_STT,
                                      'PRODUCTS' VALUE (
                                          CASE 
                                              WHEN PRODUCTS IS NOT NULL THEN (
                                                  SELECT JSON_ARRAYAGG(
                                                             JSON_OBJECT(
                                                                 'PPEQ_COUNT' VALUE PPEQ_COUNT,
                                                                 'GOOD_ID' VALUE GOOD_ID
                                                             )
                                                         )
                                                  FROM JSON_TABLE(
                                                      TO_CLOB(PRODUCTS), 
                                                      '$[*]'
                                                      COLUMNS (
                                                          PPEQ_COUNT VARCHAR2(10) PATH '$.PPEQ_COUNT',
                                                          GOOD_ID VARCHAR2(20) PATH '$.GOOD_ID'
                                                      )
                                                  )
                                              )
                                              ELSE JSON_ARRAY()
                                          END
                                      )
                                  )
                              )
                       FROM JSON_TABLE(
                            p_request_data FORMAT JSON, 
                            '$.data.Radifs[*]'
                            COLUMNS (
                                PHASE VARCHAR2(10) PATH '$.PHASE',
                                LIC_STT_NAME VARCHAR2(100) PATH '$.LIC_STT_NAME',
                                AMPER VARCHAR2(10) PATH '$.AMPER',
                                KILOWATT VARCHAR2(10) PATH '$.KILOWATT',
                                LIC_STT VARCHAR2(10) PATH '$.LIC_STT',
                                PRODUCTS VARCHAR2(4000) PATH '$.Products'
                            )
                        )
                   )
               )
        INTO v_result
        FROM dual;

        RETURN v_result;
    END ProcessRequest;

END MY_PACKAGE;

调用示例

DECLARE
    v_request_data CLOB;
    v_region_id    VARCHAR2(100);
    v_result       CLOB;
BEGIN
    v_region_id := 'A2D93E00F04A4C198EEC6477E91A9DDE';

    v_request_data := '{
        "data": {
            "REQUEST_NO": "REQ123456",
            "BRANCH_CODE": "220",
            "Radifs": [
                {
                    "PHASE": "3",
                    "Products": [
                        {
                            "PPEQ_COUNT": "27",
                            "GOOD_ID": "999999"
                        }
                    ],
                    "LIC_STT_NAME": "Service Fee",
                    "AMPER": "25",
                    "KILOWATT": "0",
                    "LIC_STT": "89"
                }
            ],
            "CITY": "Village",
            "NATIONAL_ID": "1234567890",
            "LAST_NAME": "Doe",
            "FIRST_NAME": "John",
            "PHONE": "1234567890"
        },
        "message": null,
        "error": null,
        "status": true
    }';

    v_result := MY_PACKAGE.ProcessRequest(v_request_data, v_region_id);

    DBMS_OUTPUT.PUT_LINE(v_result);
END;

非预期输出

{
  "REQUEST_INFO": {
    "geo": 2,
    "provider": "A2D93E00F04A4C198EEC6477E91A9DDE",
    "nationalId": "1234567890",
    "name": "John",
    "family": "Doe",
    "phone": "1234567890",
    "requestNo": "REQ123456",
    "subBranch": "00220"
  },
  "SUB_REQUEST": [
    {
      "PHASE": "3",
      "LIC_STT_NAME": "Service Fee",
      "AMPER": 25,
      "KILOWATT": 0,
      "LIC_STT": "89",
      "PRODUCTS": []
    }
  ]
}

预期输出

{
  "REQUEST_INFO": {
    "geo": 2,
    "provider": "A2D93E00F04A4C198EEC6477E91A9DDE",
    "nationalId": "1234567890",
    "name": "John",
    "family": "Doe",
    "phone": "1234567890",
    "requestNo": "REQ123456",
    "subBranch": "00220"
  },
  "SUB_REQUEST": [
    {
      "PHASE": "3",
      "LIC_STT_NAME": "Service Fee",
      "AMPER": 25,
      "KILOWATT": 0,
      "LIC_STT": "89",
      "PRODUCTS": [
        {
          "PPEQ_COUNT": "27",
          "GOOD_ID": "999999"
        }
      ]
    }
  ]
}

问题原因

内层JSON_TABLE未指定FORMAT JSON：处理PRODUCTS字段时，传入的是字符串类型的JSON数据，但未声明FORMAT JSON，Oracle会将其当作普通文本解析，无法匹配JSON路径$[*]，导致返回空结果，最终JSON_ARRAYAGG生成null，触发CASE分支返回空数组[]。
字段类型选择不当：将JSON数组存储到VARCHAR2列中，虽然示例数据长度足够，但长期来看存在截断风险，且不如JSON类型直观适配JSON数据。

修正方案

修改后的存储过程代码

CREATE OR REPLACE PACKAGE BODY "MY_PACKAGE" AS

    FUNCTION ProcessRequest(p_request_data IN CLOB, p_region_id IN VARCHAR2) RETURN CLOB IS
        v_result CLOB;
        v_geo    NUMBER;
        v_city   VARCHAR2(100);
    BEGIN
        SELECT json_value(p_request_data, '$.data.CITY')
        INTO v_city
        FROM dual;

        IF v_city = 'Town' THEN
            v_geo := 1;
        ELSE
            v_geo := 2;
        END IF;

        SELECT JSON_OBJECT(
                   'REQUEST_INFO' VALUE JSON_OBJECT(
                        'geo' VALUE v_geo,
                        'provider' VALUE p_region_id,
                        'nationalId' VALUE json_value(p_request_data, '$.data.NATIONAL_ID'),
                        'name' VALUE json_value(p_request_data, '$.data.FIRST_NAME'),
                        'family' VALUE json_value(p_request_data, '$.data.LAST_NAME'),
                        'phone' VALUE json_value(p_request_data, '$.data.PHONE'),
                        'requestNo' VALUE json_value(p_request_data, '$.data.REQUEST_NO'),
                        'subBranch' VALUE LPAD(json_value(p_request_data, '$.data.BRANCH_CODE'), 5, '0')
                    ),
                   'SUB_REQUEST' VALUE (
                       SELECT JSON_ARRAYAGG(
                                  JSON_OBJECT(
                                      'PHASE' VALUE PHASE,
                                      'LIC_STT_NAME' VALUE LIC_STT_NAME,
                                      'AMPER' VALUE TO_NUMBER(AMPER),
                                      'KILOWATT' VALUE TO_NUMBER(KILOWATT),
                                      'LIC_STT' VALUE LIC_STT,
                                      'PRODUCTS' VALUE (
                                          CASE 
                                              WHEN PRODUCTS IS NOT NULL THEN (
                                                  SELECT JSON_ARRAYAGG(
                                                             JSON_OBJECT(
                                                                 'PPEQ_COUNT' VALUE PPEQ_COUNT,
                                                                 'GOOD_ID' VALUE GOOD_ID
                                                             )
                                                         )
                                                  FROM JSON_TABLE(
                                                      PRODUCTS FORMAT JSON, -- 添加FORMAT JSON声明
                                                      '$[*]'
                                                      COLUMNS (
                                                          PPEQ_COUNT VARCHAR2(10) PATH '$.PPEQ_COUNT',
                                                          GOOD_ID VARCHAR2(20) PATH '$.GOOD_ID'
                                                      )
                                                  )
                                              )
                                              ELSE JSON_ARRAY()
                                          END
                                      )
                                  )
                              )
                       FROM JSON_TABLE(
                            p_request_data FORMAT JSON, 
                            '$.data.Radifs[*]'
                            COLUMNS (
                                PHASE VARCHAR2(10) PATH '$.PHASE',
                                LIC_STT_NAME VARCHAR2(100) PATH '$.LIC_STT_NAME',
                                AMPER VARCHAR2(10) PATH '$.AMPER',
                                KILOWATT VARCHAR2(10) PATH '$.KILOWATT',
                                LIC_STT VARCHAR2(10) PATH '$.LIC_STT',
                                PRODUCTS JSON PATH '$.Products' -- 将字段类型改为JSON
                            )
                        )
                   )
               )
        INTO v_result
        FROM dual;

        RETURN v_result;
    END ProcessRequest;

END MY_PACKAGE;

关键修改点

外层JSON_TABLE的PRODUCTS列类型改为JSON：直接存储JSON数组，避免字符串类型的转换和潜在截断问题。
内层JSON_TABLE添加FORMAT JSON：明确告知Oracle传入的是JSON数据，确保路径$[*]能正确匹配数组元素。
移除TO_CLOB转换：由于PRODUCTS已经是JSON类型，无需额外转换，直接传入JSON_TABLE即可。

替代兼容方案（针对不支持JSON类型的Oracle版本）

如果你的Oracle版本低于12cR2，不支持JSON类型，可以将PRODUCTS列改为CLOB类型，并保持内层FORMAT JSON声明：

-- 外层JSON_TABLE修改
PRODUCTS CLOB PATH '$.Products'

-- 内层JSON_TABLE修改
FROM JSON_TABLE(
    PRODUCTS FORMAT JSON,
    '$[*]'
    COLUMNS (...)
)

内容的提问来源于stack exchange，提问作者S.kh

Oracle PL/SQL中JSON_TABLE提取PRODUCTS返回空数组问题

原存储过程代码（泛化名） #

调用示例 #

非预期输出 #

预期输出 #

问题原因 #

修正方案 #

修改后的存储过程代码 #

关键修改点 #

替代兼容方案（针对不支持JSON类型的Oracle版本） #