This is how I am getting result in Oracle 12c
| Id | Start Date Range | End Date Range |
|---|---|---|
| 1 | [ “2019-01-07″,”2019-02-17″,”2019-03-17”] | [ “2019-01-14″,”2019-02-21″,”2019-03-21”] |
And I want it
| Id | Start Date Range | End Date Range |
|---|---|---|
| 1 | 2019-01-07 | 2019-01-14 |
| 1 | 2019-02-17 | 2019-02-21 |
| 1 | 2019-03-17 | 2019-03-21 |
Earlier I had asked this question for single-column split and below is the link How to replace special characters and then break line in oracle But when I add another column there is the cartesian product.
Advertisement
Answer
You can use json_table to extract the strings from the JSON arrays, presumably as actual dates:
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
The for ordinality clauses provide an index into each array, and the join then matches up the ‘related’ array entries.
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
If you want string rather than dates for some reason you can just change the data type in the column clauses.