By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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}]}'
);
1 rows affected
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
| NAME | FLAG | ACTION |
|---|---|---|
| XYZ | false | Manager |
| XYZ | false | Specific User List |
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
| NAME | FLAG | ACTION1 | ACTION2 |
|---|---|---|---|
| XYZ | false | Manager | Specific User List |