Extract variable fields from JSON Oracle 19c

Question

I have below JSON stored in CLOB in a table.The challenge is the fields under "Fail" are not fixed and can increase or decrease & have different names."Pass" and "err_msg" is fixed.

I want to extract "Pass","Fail" & "err_msg" final values either in column or rows either using plsql or SQL(preferable). I was trying to figure out JSON_DATAGUIDE & JSON_KEY_LIST My Oracle version is 19c

{
    "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
}

expected output :

status                                      ids
-----------------------------------------------------------
pass                                        dUZ1OvFgRWKAU
pass                                        cGauHbTWQ369w
fail-The below token is not valid           587XXXX9
fail-The below token is not valid           587XXXX0
fail-Requested token was not found.         edgDmwSxpcMhMf0C9
err_msg                                     null

Answer 1

Given the sample data:

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
}');

Then you can 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;
/

And use it and JSON_TABLE to get each status and token:

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;

Which outputs:

STATUS	TOKEN
pass	dUZ1OvFgRWKAU
pass	cGauHbTWQ369w
pass	cWcclj4oQvO4N
fail-The below token is not valid	587XXXX9
fail-The below token is not valid	556XXXX6
fail-The below token is not valid	556XXXX0
fail-Requested token was not found.	edgDmwSxpcMhMf0C9
fail-Requested token was not found.	d8T8S62SJw
err_msg	null

fiddle