如何在PL/SQL中从选中的JSON数组提取role_id值？

首先，你的原查询存在一个问题：用JSON_ARRAY()包裹了完整的JSON字符串，导致最终输出是包含JSON字符串的单元素数组，而非直接的JSON对象数组。要提取role_id，需先将嵌套的字符串解析为JSON结构，再进行提取。

方案1：修正原查询（推荐） #

直接构造标准的JSON对象数组，避免嵌套字符串的问题：

SELECT  
    JSON_ARRAY(
        JSON_OBJECT('role_id' VALUE 'TEST1', 'role_name' VALUE 'Для тестів 1'),
        JSON_OBJECT('role_id' VALUE 'TEST3', 'role_name' VALUE 'Для тестів 3')
    ) AS l
FROM dual;

输出为标准的JSON对象数组：

[
    {"role_id": "TEST1", "role_name": "Для тестів 1"},
    {"role_id": "TEST3", "role_name": "Для тестів 3"}
]

之后用JSON_TABLE直接提取role_id：

SELECT j.role_id
FROM (
    SELECT  
        JSON_ARRAY(
            JSON_OBJECT('role_id' VALUE 'TEST1', 'role_name' VALUE 'Для тестів 1'),
            JSON_OBJECT('role_id' VALUE 'TEST3', 'role_name' VALUE 'Для тестів 3')
        ) AS l
    FROM dual
) t,
JSON_TABLE(t.l, '$[*]' COLUMNS role_id VARCHAR2(100) PATH '$.role_id') j;

方案2：基于现有嵌套输出提取 #

如果必须基于当前查询的嵌套输出，需先提取字符串元素，再解析为JSON数组，最后提取role_id：

SELECT j.role_id
FROM (
    -- 原查询结果
    SELECT  
        JSON_ARRAY(
        '[
        {
            "role_id": "TEST1",
            "role_name": "Для тестів 1"
        },
        {
            "role_id": "TEST3",
            "role_name": "Для тестів 3"
        }
        ]') AS l
    FROM dual
) t,
-- 提取数组中的字符串元素
JSON_TABLE(t.l, '$[*]' COLUMNS json_str CLOB PATH '$') s,
-- 将字符串解析为JSON数组并提取role_id
JSON_TABLE(s.json_str, '$[*]' COLUMNS role_id VARCHAR2(100) PATH '$.role_id') j;

执行后结果：

ROLE_ID
-------
TEST1
TEST3

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