Oracle parse an array of object

Question

I have a problem to parse a json correctly. having this:

CREATE TABLE fb_tab
(col CLOB, constraint json_con_1 CHECK (col IS JSON),
 id number null);

insert into fb_tab (col)
values('
{"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"}]}}}]}
');
commit;

i'm trying to use this query to parse:

select a.*
FROM fb_tab t
    ,JSON_TABLE(col,
        '$.items[*]'
        COLUMNS (
            customerId          varchar2(100) PATH '$.customerId',
            NESTED PATH         '$.schedule."2021-03-24".freeTime[*]' COLUMNS (   
                inicio VARCHAR2 (8) PATH '$[0]',
                fin VARCHAR2 (8) PATH '$[1]'
            ),
            NESTED PATH         '$.schedule."2021-03-24".arrivalTime[*]' COLUMNS (   
                minStartTime VARCHAR2 (8) PATH '$.min',
                maxStartTime VARCHAR2 (8) PATH '$.max'
            )
        )
    ) as a
where id = 1;

and the result is not correct:

the result must be :

Can somebody give an advise?

Best Regards

Answer 1

You can use two JSON_TABLEs and then correlate the join condition between the two:

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;

Which, for your sample data:

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"}
          ]
        }
      }
    }
  ]
}'
);

Outputs:

CUSTOMERID | INICIO | FIN   | MINSTARTTIME | MAXSTARTTIME
:--------- | :----- | :---- | :----------- | :-----------
24929      | 09:00  | 09:30 | 09:30        | 10:30       
24929      | 11:00  | 18:00 | 11:30        | 16:30       

db<>fiddle here