By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE fb_tab(
col CLOB,
constraint json_con_1 CHECK (col IS JSON),
id number null
);
insert into fb_tab (
id,
col
) values (
1,
'{
"totalResults":1,
"limit":100,
"offset":0,
"items":[
{
"customerId":"24929",
"schedule":{
"2021-03-24":{
"freeTime":[
["09:00","09:30"],["11:00","18:00"]
],
"arrivalTime":[
{"min":"09:30","max":"10:30"},{"min":"11:30","max":"16:30"}
]
}
}
}
]
}'
);
1 rows affected
SELECT a.customerid,
a.inicio,
a.fin,
s.minstarttime,
s.maxstarttime
FROM fb_tab t
CROSS APPLY JSON_TABLE(
t.col,
'$.items[*]'
COLUMNS (
item_rn FOR ORDINALITY,
customerId varchar2(100) PATH '$.customerId',
NESTED PATH '$.schedule."2021-03-24".freeTime[*]' COLUMNS (
ft_rn FOR ORDINALITY,
inicio VARCHAR2(8) PATH '$[0]',
fin VARCHAR2(8) PATH '$[1]'
)
)
) a
CROSS APPLY JSON_TABLE(
t.col,
'$.items[*]'
COLUMNS (
item_rn FOR ORDINALITY,
NESTED PATH '$.schedule."2021-03-24".arrivalTime[*]' COLUMNS (
at_rn FOR ORDINALITY,
minStartTime VARCHAR2(8) PATH '$.min',
maxStartTime VARCHAR2(8) PATH '$.max'
)
)
) s
where id = 1
AND a.item_rn = s.item_rn
AND a.ft_rn = s.at_rn;
| CUSTOMERID | INICIO | FIN | MINSTARTTIME | MAXSTARTTIME |
|---|---|---|---|---|
| 24929 | 09:00 | 09:30 | 09:30 | 10:30 |
| 24929 | 11:00 | 18:00 | 11:30 | 16:30 |