Forum Stats

  • 3,826,357 Users
  • 2,260,635 Discussions
  • 7,896,916 Comments

Discussions

How to generate JSON format data from table

user525840
user525840 Member Posts: 9 Blue Ribbon

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?

Tagged:

Best Answer

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,471 Red Diamond
    Answer ✓
    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.

Answers

  • odie_63
    odie_63 Member Posts: 8,492 Silver Trophy
    edited Jun 7, 2022 1:47PM

    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.

  • Paulzip
    Paulzip Member Posts: 8,693 Blue Diamond

    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
        }
      ]
    }
    
     
    
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,471 Red Diamond
    Answer ✓
    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.

  • user525840
    user525840 Member Posts: 9 Blue Ribbon

    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.

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,471 Red Diamond

    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.