For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!
I have to display output which is grouped based on month and its corresponding array values as json_ojbect. Then you have to actually aggregate JSON objects per month in a JSON array : use aggregate function JSON_ARRAYAGG. Final step is to consolidate all months in a single object, that's an aggregation too : use JSON_OBJECTAGG. For example (just showing the last query step) :
SELECT json_objectagg( cal_mon value json_arrayagg( json_object( 'date' value to_char(cal_dates,'DD-MON-YYYY') , 'val' value nvl(appointment_id,0) ) order by cal_dates -- if necessary ) ) Final_output FROM get_json GROUP BY cal_mon
You may also need to add some RETURNING CLOB clause here and there if the output is too large.
Maybe something like this (might need tweaking).
with cal_data as ( select first_date + level - 1 as cal_dates from (select next_day(trunc(sysdate, 'MONTH') - 15, 'SUNDAY') as first_date , next_day(last_day(sysdate) - 1, 'SATURDAY') as last_date from dual) connect by level <= last_date + 1 - first_date ) , data as ( select c.*, a.*, to_number(to_char(cal_dates, 'dd')) day_of_week, to_char(cal_dates, 'fmMonth') month_name, to_number(to_char(cal_dates, 'MM')) month_num from cal_data c left join patient_appointments a on a.appointment_date >= c.cal_dates and a.appointment_date < c.cal_dates + 1 ) select json_objectagg( month_name , json_arrayagg( json_object ( 'date' value day_of_week , 'val' value nvl(appointment_id, 0) ) order by day_of_week ) ) from data group by month_num, month_name order by month_num ; { "May" : [ { "date" : 22, "val" : 0 }, { "date" : 23, "val" : 0 }, { "date" : 24, "val" : 0 }, { "date" : 25, "val" : 0 }, { "date" : 26, "val" : 0 }, { "date" : 27, "val" : 0 }, { "date" : 28, "val" : 0 }, { "date" : 29, "val" : 0 }, { "date" : 30, "val" : 0 }, { "date" : 31, "val" : 0 } ], "June" : [ { "date" : 1, "val" : 0 }, { "date" : 2, "val" : 0 }, { "date" : 3, "val" : 0 }, { "date" : 4, "val" : 0 }, { "date" : 5, "val" : 0 }, { "date" : 6, "val" : 0 }, { "date" : 7, "val" : 1 }, { "date" : 8, "val" : 0 }, { "date" : 9, "val" : 2 }, { "date" : 10, "val" : 3 }, { "date" : 11, "val" : 0 }, { "date" : 12, "val" : 0 }, { "date" : 13, "val" : 0 }, { "date" : 14, "val" : 0 }, { "date" : 15, "val" : 0 }, { "date" : 16, "val" : 0 }, { "date" : 17, "val" : 0 }, { "date" : 18, "val" : 0 }, { "date" : 19, "val" : 0 }, { "date" : 20, "val" : 0 }, { "date" : 21, "val" : 0 }, { "date" : 22, "val" : 0 }, { "date" : 23, "val" : 0 }, { "date" : 24, "val" : 0 }, { "date" : 25, "val" : 0 }, { "date" : 26, "val" : 0 }, { "date" : 27, "val" : 0 }, { "date" : 28, "val" : 0 }, { "date" : 29, "val" : 0 }, { "date" : 30, "val" : 0 } ], "July" : [ { "date" : 1, "val" : 0 }, { "date" : 2, "val" : 0 } ] }
WITH CAL_DATA AS ( SELECT FIRST_DATE + LEVEL -1 AS CAL_DATES FROM ( SELECT NEXT_DAY(TRUNC(SYSDATE,'MONTH') - 15 ,'SUNDAY') AS FIRST_DATE, NEXT_DAY(LAST_DAY(SYSDATE) - 1,'SATURDAY') AS LAST_DATE FROM DUAL ) CONNECT BY LEVEL <= LAST_DATE + 1 - FIRST_DATE ), GET_JSON AS ( SELECT TO_CHAR(C.CAL_DATES,'MON') CAL_MON, C.CAL_DATES, A.* FROM CAL_DATA C LEFT JOIN PATIENT_APPOINTMENTS A ON (TRUNC(C.CAL_DATES) = TRUNC(A.APPOINTMENT_DATE)) ) SELECT JSON_SERIALIZE( JSON_OBJECT( KEY CAL_MON VALUE JSON_ARRAYAGG( JSON_OBJECT( KEY 'date' VALUE TO_CHAR(CAL_DATES,'DD-MON-YYYY'), KEY 'val' VALUE NVL(APPOINTMENT_ID,0) ) ORDER BY CAL_DATES ) ) RETURNING CLOB PRETTY ) Final_output FROM GET_JSON GROUP BY CAL_MON ORDER BY CAL_MON / FINAL_OUTPUT ------------------------------------------------------------------------------------------ { "JUL" : [ { "date" : "01-JUL-2022", "val" : 0 }, { "date" : "02-JUL-2022", "val" : 0 } ] } { "JUN" : [ { "date" : "01-JUN-2022", "val" : 0 }, { "date" : "02-JUN-2022", "val" : 0 }, { "date" : "03-JUN-2022", "val" : 0 }, { "date" : "04-JUN-2022", "val" : 0 }, { "date" : "05-JUN-2022", "val" : 0 }, { "date" : "06-JUN-2022", "val" : 0 }, { "date" : "07-JUN-2022", "val" : 1 }, { "date" : "08-JUN-2022", "val" : 0 }, { "date" : "09-JUN-2022", "val" : 2 }, { "date" : "10-JUN-2022", "val" : 3 }, { "date" : "11-JUN-2022", "val" : 0 }, { "date" : "12-JUN-2022", "val" : 0 }, { "date" : "13-JUN-2022", "val" : 0 }, { "date" : "14-JUN-2022", "val" : 0 }, { "date" : "15-JUN-2022", "val" : 0 }, { "date" : "16-JUN-2022", "val" : 0 }, { "date" : "17-JUN-2022", "val" : 0 }, { "date" : "18-JUN-2022", "val" : 0 }, { "date" : "19-JUN-2022", "val" : 0 }, { "date" : "20-JUN-2022", "val" : 0 }, { "date" : "21-JUN-2022", "val" : 0 }, { "date" : "22-JUN-2022", "val" : 0 }, { "date" : "23-JUN-2022", "val" : 0 }, { "date" : "24-JUN-2022", "val" : 0 }, { "date" : "25-JUN-2022", "val" : 0 }, { "date" : "26-JUN-2022", "val" : 0 }, { "date" : "27-JUN-2022", "val" : 0 }, { "date" : "28-JUN-2022", "val" : 0 }, { "date" : "29-JUN-2022", "val" : 0 }, { "date" : "30-JUN-2022", "val" : 0 } ] } { "MAY" : [ { "date" : "22-MAY-2022", "val" : 0 }, { "date" : "23-MAY-2022", "val" : 0 }, { "date" : "24-MAY-2022", "val" : 0 }, { "date" : "25-MAY-2022", "val" : 0 }, { "date" : "26-MAY-2022", "val" : 0 }, { "date" : "27-MAY-2022", "val" : 0 }, { "date" : "28-MAY-2022", "val" : 0 }, { "date" : "29-MAY-2022", "val" : 0 }, { "date" : "30-MAY-2022", "val" : 0 }, { "date" : "31-MAY-2022", "val" : 0 } ] } SQL>
SY.
Thank you everyone for quick turnaround. Using the suggested query, I can see the expected output. I will now pass it to the Oracle JET web component to use this json data instead of static json file. I will create a procedure returning clob output from this SQL and use that in array data provider in front end. Thank you.
Keep in mind NEXT_DAY is NLS dependent so if your stored procedure will be executed by, say, client in Israel:
SQL> create or replace 2 procedure p1 3 is 4 begin 5 dbms_output.put_line(NEXT_DAY(TRUNC(SYSDATE,'MONTH') - 15 ,'SUNDAY')); 6 end; 7 / Procedure created. SQL> set serveroutput on SQL> exec p1; 22-MAY-22 PL/SQL procedure successfully completed. SQL> alter session set nls_date_language=hebrew; Session altered. SQL> exec p1; BEGIN p1; END; * ERROR at line 1: ORA-01846: not a valid day of the week ORA-06512: at "SY47755.P1", line 4 ORA-06512: at line 1 SQL>
You could use 'IW' format instead but it is simpler to use pre-defined date (any Sunday):
TRUNC(SYSDATE,'MONTH') - 15 + 1 + MOD(ABS(TRUNC(SYSDATE,'MONTH') - 15 - DATE '2022-05-29') - 1,7)
Similarly you can get next Saturday (or any day). SY.