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.
If I try
select JSON_VALUE(JSON_CONTENT,'$.action')JSON_CONTENT from test
I get NULL.
I read that JSON_VALUE only supports 1 entry .
Is there any workaround to get both values of action ?
You have a few options, depending on what you want the output to be:
Access by Array PositionYou can do this using either json_table or dot-notation access:
create table t (
c1 varchar2(100)
check ( c1 is json )
);
insert into t values ( '{
"relist":[{"name":"XYZ","action":["Manager","Specific User List"],"flag":false}]
}' );
commit;
select t.c1.relist.action[0] v1,
json_value (
c1, '$.relist.action[1]'
) v2
from t t;
V1 V2
Manager Specific User List Return the Whole ArrayUse JSON_query to return the entire array:
select json_query (
c1, '$.relist.action'
) arr
from t t;
ARR
["Manager","Specific User List"]
Convert the Elements to RowsWith JSON_table, you can generate a row for each element in the array:
select j.* from t, json_table (
c1, '$.relist.action[*]'
columns (
v path '$'
)
) j;
V
Manager
Specific User List