Forum Stats

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

Discussions

Categories

How to generate JSON format data from table

user525840
user525840 Member Posts: 9 Blue Ribbon
in SQL & PL/SQL

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:
· Share on TwitterShare on Facebook

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.

    · Share on TwitterShare on Facebook

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.

    · Share on TwitterShare on Facebook
  • 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
    }
  ]
}

 
    · Share on TwitterShare on Facebook
  • 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.

    · Share on TwitterShare on Facebook
  • 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.

    · Share on TwitterShare on Facebook
  • 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.

    · Share on TwitterShare on Facebook