优化Oracle JSON转嵌套表类型查询：避免重复调用JSON_TABLE

现有Oracle查询可将JSON格式的l_clob_response解析为自定义嵌套表类型to_dncl_verification，查询能正常返回预期结果，但当前实现两次调用JSON_TABLE解析同一份JSON数据，以下是优化方案。

原查询代码 #

SELECT to_dncl_verification(status_code   => t.status,
                            d_valid_from  => to_date(t.d_valid_from, 'yyyy-mm-dd'),
                            d_valid_to    => to_date(t.d_valid_to, 'yyyy-mm-dd'),
                            category_list => CAST(
                                                  MULTISET (SELECT tc.category, CASE WHEN tc.allowed = 'true' THEN 1 ELSE 0 END
                                                              FROM JSON_TABLE(l_clob_response, '$'
                                                                   COLUMNS NESTED PATH '$.categories[*]'
                                                                     COLUMNS (category VARCHAR2(255) PATH '$.category',
                                                                              allowed  VARCHAR2(255) PATH '$.allowed')
                                                                   ) tc 
                                                            ) AS tc_dncl_category
                                                     )
                               )
      INTO y_verification_result
      FROM JSON_TABLE(l_clob_response, '$'
                      COLUMNS status       VARCHAR2(255) PATH '$.status',
                              d_valid_from VARCHAR2(255) PATH '$.dateValidFrom',
                              d_valid_to   VARCHAR2(255) PATH '$.dateValidTo'
                      ) t;

自定义类型定义 #

create or replace type to_dncl_category is object (
  category_code   varchar2(20),
  is_allowed      number
);

create or replace type tc_dncl_category is table of to_dncl_category;

create or replace type to_dncl_verification is object (
  status_code          varchar2(20),
  d_valid_from         date,
  d_valid_to           date,
  category_list        tc_dncl_category  
);

JSON数据示例 #

{
    "id": "123",
    "status": "PARTIALLY_BLOCKED",
    "dateOfCheck": "2023-01-01",
    "dateValidFrom": "2023-05-15",
    "categories": [
        {
            "id": "123",
            "category": "category ABC",
            "allowed": true,
            "dateCreated": "2023-05-05T10:47:19.745Z",
            "recordVersion": 0
        },
        {
            "id": "123",
            "category": "category DEF",
            "allowed": false,
            "dateCreated": "2023-05-05T10:47:19.745Z",
            "recordVersion": 0
        },
        {
            "id": "123",
            "category": "category GHI",
            "allowed": true,
            "dateCreated": "2023-05-05T10:47:19.745Z",
            "recordVersion": 0
        }
    ],
    "dateValidTo": "2023-05-30",
    "recordVersion": 0
}

优化后的查询 #

通过单次JSON_TABLE调用同时解析根节点字段和嵌套数组，再分组聚合生成嵌套集合，避免重复解析：

SELECT to_dncl_verification(
         status_code   => MAX(t.status),
         d_valid_from  => TO_DATE(MAX(t.d_valid_from), 'yyyy-mm-dd'),
         d_valid_to    => TO_DATE(MAX(t.d_valid_to), 'yyyy-mm-dd'),
         category_list => CAST(
                            COLLECT(to_dncl_category(t.category, CASE WHEN t.allowed = 'true' THEN 1 ELSE 0 END))
                            AS tc_dncl_category
                          )
       )
INTO y_verification_result
FROM JSON_TABLE(
       l_clob_response,
       '$'
       COLUMNS (
         status       VARCHAR2(255) PATH '$.status',
         d_valid_from VARCHAR2(255) PATH '$.dateValidFrom',
         d_valid_to   VARCHAR2(255) PATH '$.dateValidTo',
         NESTED PATH '$.categories[*]'
         COLUMNS (
           category VARCHAR2(255) PATH '$.category',
           allowed  VARCHAR2(255) PATH '$.allowed'
         )
       )
) t
GROUP BY 1; -- 因JSON为单个对象，所有行的根字段值一致，用常量分组即可

优化说明 #

1. 单次JSON解析：仅调用一次JSON_TABLE，同时提取根节点字段和嵌套categories数组的元素，避免重复读取解析同一JSON数据。
2. 聚合生成嵌套集合：用COLLECT函数将所有category记录聚合为tc_dncl_category类型集合，替代原查询中嵌套的MULTISET+JSON_TABLE组合。
3. 分组处理：NESTED PATH会为每个category生成一行数据，通过GROUP BY将同一根对象的所有行聚合，用MAX取值是因为所有行的根字段值完全相同，保证结果正确。

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