JSON Value - Oracle PL/SQL : Multiple Fields

I have a HCLOB with below sample entry

"relist":[{"name":"XYZ","action":["Manager","Specific User List"],"flag":false}]

When I try to get name or flag using JSON_VALUE I am able to get it as it has single field , but I want to get the value for action . I read that JSON_VALUE only supports 1 entry .

Is there any workaround to get both values of action ?

Regards.

Use JSON_TABLE with a nested path:

SELECT j.*
FROM   table_name t
       CROSS APPLY JSON_TABLE(
         t.data,
         '$.relist[*]'
         COLUMNS (
           name VARCHAR2(20) PATH '$.name',
           flag VARCHAR2(5)  PATH '$.flag',
           NESTED PATH '$.action[*]' COLUMNS(
             action VARCHAR2(20) PATH '$'
           )
         )
       ) j

Which, for the sample data:

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

INSERT INTO table_name ( data ) VALUES (
  '{"relist":[{"name":"XYZ","action":["Manager","Specific User List"],"flag":false}]}'
);

Outputs:

NAME | FLAG  | ACTION            
:--- | :---- | :-----------------
XYZ  | false | Manager           
XYZ  | false | Specific User List

Or use the indices of the array to get the actions:

SELECT j.*
FROM   table_name t
       CROSS APPLY JSON_TABLE(
         t.data,
         '$.relist[*]'
         COLUMNS (
           name    VARCHAR2(20) PATH '$.name',
           flag    VARCHAR2(5)  PATH '$.flag',
           action1 VARCHAR2(20) PATH '$.action[0]',
           action2 VARCHAR2(20) PATH '$.action[1]'
         )
       ) j

Which outputs:

NAME | FLAG  | ACTION1 | ACTION2           
:--- | :---- | :------ | :-----------------
XYZ  | false | Manager | Specific User List

db<>fiddle here