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
The value for "action" is a JSON array. You accepted an answer that returns the scalar values from that array, rather than the array itself (as a single, compound value). Whether that was really your requirement or not (in other words: whether you misstated your question or not), here is how you could answer the problem exactly as you stated it.
As you understood correctly, JSON_VALUE() only returns scalar values from a JSON document. To return JSON fragments, you can use JSON_QUERY(), essentially with the same syntax.
To return the JSON array which is the value of "action" in your document (using MT0's table):
select json_query(data, '$.relist.action') as action
from table_name
;
ACTION
------------------------------------
["Manager","Specific User List"]