I'd like to use an input parameter that is a json array: [1,2,3]. How can access this data as a collection or convert it to a collection?
with jdata as (
select treat(p_input_json as json) as jrow
from dual
)
select count(*)
into vCount
from my_test
where i in (
select jrow --jrow.?
from jdata
);
In my live sql script, I want to see a "1" for the second line of output.
thanx in advance, d
Are you're on 19c, you can map JSON documents to SQL arrays (nested tables or varrays) or objects. Just create this type and specify it in the returning clause:
create or replace type num_arr
as table of number;
/
with jdata as (
select treat('[1,2,3]' as json) as jrow
from dual
)
select column_value
from jdata
cross apply json_value (
jrow, '$' returning num_arr
) jv;
COLUMN_VALUE
1
2
3 On older versions - or if you want to avoid creating an extra object - you can use json_table to convert the array to rows:
with jdata as (
select treat('[1,2,3]' as json) as jrow
from dual
)
select v
from jdata
cross apply json_table (
jrow, '$[*]' columns ( v path '$' )
) jv;
V
1
2
3