I am planning to use json format data via REST API to Oracle JET web component. However the data is not coming as per expected format.
Expected format is:
{
"january": [
{
"date": 1,
"value": 39
},
{
"date": 2,
"value": 42
},
{
"date": 3,
"value": 42
}
],
"february": [
{
"date": 1,
"value": 36
},
{
"date": 2,
"value": 34
},
{
"date": 3,
"value": 26
},
{
"date": 4,
"value": 43
}
],
"march": [
{
"date": 1,
"value": 31
},
{
"date": 2,
"value": 39
},
{
"date": 3,
"value": 37
},
{
"date": 4,
"value": 45
}
]
};
Sample table and data preparation:
CREATE TABLE PATIENT_APPOINTMENTS
(APPOINTMENT_ID NUMBER,
APPOINTMENT_COMPANY_ID NUMBER,
APPOINTMENT_CLINIC_ID NUMBER,
APPOINTMENT_PATIENT_ID NUMBER,
APPOINTMENT_DATE DATE,
APPOINTMENT_TIME DATE,
APPOINTMENT_PATIENT_COMPLAINT VARCHAR2(100)
);
INSERT INTO PATIENT_APPOINTMENTS(APPOINTMENT_ID, APPOINTMENT_COMPANY_ID, APPOINTMENT_CLINIC_ID, APPOINTMENT_PATIENT_ID,
APPOINTMENT_DATE, APPOINTMENT_TIME, APPOINTMENT_PATIENT_COMPLAINT)
VALUES(1,1,1,1,SYSDATE, SYSDATE, 'TOOTH PAIN');
INSERT INTO PATIENT_APPOINTMENTS(APPOINTMENT_ID, APPOINTMENT_COMPANY_ID, APPOINTMENT_CLINIC_ID, APPOINTMENT_PATIENT_ID,
APPOINTMENT_DATE, APPOINTMENT_TIME, APPOINTMENT_PATIENT_COMPLAINT)
VALUES(2,1,1,1,SYSDATE+2, SYSDATE+2, 'TOOTH PAIN');
INSERT INTO PATIENT_APPOINTMENTS(APPOINTMENT_ID, APPOINTMENT_COMPANY_ID, APPOINTMENT_CLINIC_ID, APPOINTMENT_PATIENT_ID,
APPOINTMENT_DATE, APPOINTMENT_TIME, APPOINTMENT_PATIENT_COMPLAINT)
VALUES(3,1,1,1,SYSDATE+3, SYSDATE+3, 'TOOTH PAIN');
My SQL Query is:
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 * FROM GET_JSON
SELECT JSON_OBJECT( cal_mon
VALUE
JSON_ARRAY(JSON_OBJECT(KEY 'date:' VALUE TO_CHAR(CAL_DATES,'DD-MON-YYYY'), KEY 'val:' VALUE NVL(APPOINTMENT_ID,0)))
) Final_output
FROM GET_JSON
--GROUP BY CAL_MON
ORDER BY CAL_DATES;
Current output (subset of output):
{"MAY":[{"date:":"30-MAY-2022","val:":0}]}
{"MAY":[{"date:":"31-MAY-2022","val:":0}]}
{"JUN":[{"date:":"01-JUN-2022","val:":0}]}
{"JUN":[{"date:":"02-JUN-2022","val:":0}]}
{"JUN":[{"date:":"03-JUN-2022","val:":0}]}
{"JUN":[{"date:":"04-JUN-2022","val:":0}]}
{"JUN":[{"date:":"05-JUN-2022","val:":0}]}
{"JUN":[{"date:":"06-JUN-2022","val:":0}]}
{"JUN":[{"date:":"07-JUN-2022","val:":1}]}
{"JUN":[{"date:":"08-JUN-2022","val:":0}]}
{"JUN":[{"date:":"09-JUN-2022","val:":2}]}
{"JUN":[{"date:":"10-JUN-2022","val:":3}]}
I have to display output which is grouped based on month and its corresponding array values as json_ojbect.
Can you please help with query correction?