如何在Oracle APEX中获取嵌套JSON中的数据

针对你给出的嵌套JSON结构，下面是两种常用的数据提取方法：

方式一：使用APEX_JSON包（PL/SQL方式） #

通过APEX_JSON的解析和遍历方法，逐层提取嵌套数组中的数据：

DECLARE
    l_json CLOB := '{
        "payload": [
            {
                "itemId": "155364958",
                "title": "N1000001",
                "terms": [
                    {
                        "title": "Price",
                        "fieldId": "PRICE",
                        "valueTypeName": "Money",
                        "isMultiValue": false,
                        "acceptableValues": "LimitedRange",
                        "isRequirement": false
                    }]
            }] }';
    l_payload_count NUMBER;
    l_terms_count NUMBER;
BEGIN
    -- 解析JSON内容
    APEX_JSON.parse(l_json);
    
    -- 获取payload数组的元素总数
    l_payload_count := APEX_JSON.get_count(p_path => 'payload');
    
    -- 遍历payload数组的每个元素
    FOR i IN 1..l_payload_count LOOP
        DBMS_OUTPUT.PUT_LINE('Item ID: ' || APEX_JSON.get_varchar2(p_path => 'payload[%d].itemId', p0 => i));
        DBMS_OUTPUT.PUT_LINE('Title: ' || APEX_JSON.get_varchar2(p_path => 'payload[%d].title', p0 => i));
        
        -- 获取当前payload元素下terms数组的元素总数
        l_terms_count := APEX_JSON.get_count(p_path => 'payload[%d].terms', p0 => i);
        
        -- 遍历terms数组的每个元素
        FOR j IN 1..l_terms_count LOOP
            DBMS_OUTPUT.PUT_LINE('Term Title: ' || APEX_JSON.get_varchar2(p_path => 'payload[%d].terms[%d].title', p0 => i, p1 => j));
            DBMS_OUTPUT.PUT_LINE('Field ID: ' || APEX_JSON.get_varchar2(p_path => 'payload[%d].terms[%d].fieldId', p0 => i, p1 => j));
            DBMS_OUTPUT.PUT_LINE('Value Type: ' || APEX_JSON.get_varchar2(p_path => 'payload[%d].terms[%d].valueTypeName', p0 => i, p1 => j));
        END LOOP;
    END LOOP;
END;
/

方式二：使用SQL的JSON_TABLE函数（查询方式） #

如果需要通过SQL直接查询提取数据，可通过多层JSON_TABLE展开嵌套数组：

SELECT
    p.item_id,
    p.item_title,
    t.term_title,
    t.field_id,
    t.value_type_name,
    t.is_multi_value,
    t.acceptable_values,
    t.is_requirement
FROM
    JSON_TABLE(
        '{
            "payload": [
                {
                    "itemId": "155364958",
                    "title": "N1000001",
                    "terms": [
                        {
                            "title": "Price",
                            "fieldId": "PRICE",
                            "valueTypeName": "Money",
                            "isMultiValue": false,
                            "acceptableValues": "LimitedRange",
                            "isRequirement": false
                        }]
                }] }',
        '$.payload[*]'
        COLUMNS (
            item_id VARCHAR2(20) PATH '$.itemId',
            item_title VARCHAR2(20) PATH '$.title',
            terms_data CLOB FORMAT JSON PATH '$.terms'
        )
    ) p,
    JSON_TABLE(
        p.terms_data,
        '$[*]'
        COLUMNS (
            term_title VARCHAR2(20) PATH '$.title',
            field_id VARCHAR2(20) PATH '$.fieldId',
            value_type_name VARCHAR2(20) PATH '$.valueTypeName',
            is_multi_value VARCHAR2(5) PATH '$.isMultiValue',
            acceptable_values VARCHAR2(20) PATH '$.acceptableValues',
            is_requirement VARCHAR2(5) PATH '$.isRequirement'
        )
    ) t;

或者更简洁的单JSON_TABLE写法（Oracle 12c及以上版本支持）：

SELECT
    jt.item_id,
    jt.item_title,
    jt.term_title,
    jt.field_id,
    jt.value_type_name,
    jt.is_multi_value,
    jt.acceptable_values,
    jt.is_requirement
FROM
    JSON_TABLE(
        '{
            "payload": [
                {
                    "itemId": "155364958",
                    "title": "N1000001",
                    "terms": [
                        {
                            "title": "Price",
                            "fieldId": "PRICE",
                            "valueTypeName": "Money",
                            "isMultiValue": false,
                            "acceptableValues": "LimitedRange",
                            "isRequirement": false
                        }]
                }] }',
        '$.payload[*].terms[*]'
        COLUMNS (
            item_id VARCHAR2(20) PATH '../../itemId',
            item_title VARCHAR2(20) PATH '../../title',
            term_title VARCHAR2(20) PATH '$.title',
            field_id VARCHAR2(20) PATH '$.fieldId',
            value_type_name VARCHAR2(20) PATH '$.valueTypeName',
            is_multi_value VARCHAR2(5) PATH '$.isMultiValue',
            acceptable_values VARCHAR2(20) PATH '$.acceptableValues',
            is_requirement VARCHAR2(5) PATH '$.isRequirement'
        )
    ) jt;

内容的提问来源于stack exchange，提问作者Programming with saadmalik