By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table your_table (id, start_range, end_range) as
select 1,
to_clob('[ "2019-01-07","2019-02-17","2019-03-17"]'),
to_clob('[ "2019-01-14","2019-02-21","2019-03-21"]')
from dual;
1 rows affected
select t.id, s.n, s.start_date, e.end_date
from your_table t
cross apply json_table (
t.start_range, '$[*]'
columns
n for ordinality,
start_date date path '$'
) s
join json_table (
t.end_range, '$[*]'
columns
n for ordinality,
end_date date path '$'
) e
on e.n = s.n
| ID | N | START_DATE | END_DATE |
|---|---|---|---|
| 1 | 1 | 07-JAN-19 | 14-JAN-19 |
| 1 | 2 | 17-FEB-19 | 21-FEB-19 |
| 1 | 3 | 17-MAR-19 | 21-MAR-19 |
select t.id, s.n, s.start_date, e.end_date
from your_table t
cross apply json_table (
t.start_range, '$[*]'
columns
n for ordinality,
start_date varchar2(10) path '$'
) s
join json_table (
t.end_range, '$[*]'
columns
n for ordinality,
end_date varchar2(10) path '$'
) e
on e.n = s.n
| ID | N | START_DATE | END_DATE |
|---|---|---|---|
| 1 | 1 | 2019-01-07 | 2019-01-14 |
| 1 | 2 | 2019-02-17 | 2019-02-21 |
| 1 | 3 | 2019-03-17 | 2019-03-21 |