给定样本数据：

CREATE TABLE data (json CLOB CHECK(json IS JSON));

INSERT INTO data (JSON) VALUES ('{
    "res": {
        "Pass": ["dUZ1OvFgRWKAU","cGauHbTWQ369w","cWcclj4oQvO4N"],
        "Fail": {
            "The below token is not valid": ["587XXXX9","556XXXX6","556XXXX0"],
            "Requested token was not found.": ["edgDmwSxpcMhMf0C9","d8T8S62SJw"]
        }
    },
    "err_msg": null
}');

然后你可以create a PL/SQL function to dynamically get the key of a JSON object：

CREATE FUNCTION get_key(
  pos  IN PLS_INTEGER,
  json IN CLOB
) RETURN VARCHAR2 
AS
  doc_keys JSON_KEY_LIST;
BEGIN
  doc_keys := JSON_OBJECT_T.PARSE ( json ).GET_KEYS;
  RETURN doc_keys( pos );
END get_key;
/

然后使用它和JSON_TABLE来获取每个状态和令牌：

SELECT 'pass' AS status,
       j.token
FROM   data d
       CROSS APPLY JSON_TABLE(
         d.json,
         '$.res.Pass[*]'
         COLUMNS (
           token VARCHAR2(100) PATH '$'
         )
       ) j
UNION ALL
SELECT 'fail-' || get_key(j.pos, j.fail),
       j.token
FROM   data d
       CROSS APPLY JSON_TABLE(
         d.json,
         '$.res.Fail'
         COLUMNS (
           fail CLOB FORMAT JSON PATH '$',
           NESTED PATH '$.*' COLUMNS (
             pos           FOR ORDINALITY,
             NESTED PATH '$[*]' COLUMNS (
               token VARCHAR2(100) PATH '$'
             )
           )
         )
       ) j
UNION ALL
SELECT 'err_msg',
       JSON_VALUE(d.json, '$.err_msg')
FROM   data d;

其输出：
| 状态|代币|
| --------------|--------------|
| 传球|dUZ1OvFgRWKAU|
| 传球|cGauHbTWQ369w|
| 传球|cWcclj4oQvO4N|
| 失败-以下令牌无效|587XXXX9|
| 失败-以下令牌无效|556XXXX6|
| 失败-以下令牌无效|556XXXX0|
| fail-Requested令牌未找到。|edgDmwSxpcMhMf0C9|
| fail-Requested令牌未找到。|d8T8S62SJW|
| 错误消息|联系我们|
fiddle