チャートのソースとなるJSONを生成する

generate-picto-chart-source.sql

declare
    l_response_json json_array_t;
    l_response clob;
    l_object json_object_t;
    l_days json_array_t;
    l_day pls_integer;
    l_date_str varchar2(7);
    l_start_date date;
    l_end_date   date;
begin
    /* 表示に使用する期間 */
    l_start_date := to_date(:P1_YEAR || case when length(:P1_MONTH) = 1 then '0' else '' end || :P1_MONTH, 'YYYYMM');
    l_end_date := add_months(l_start_date, :G_MONTH) - 1;

    /* JET Picto Chartが扱う形式で出力 */
    l_response_json := json_array_t();
    for r in (
        select
            trunc(date_rec, 'MONTH') year_month
            ,json_arrayagg(
                json_object(
                    key 'date' value extract(day from date_rec)
                    ,key 'value' value temperature_2m_max
                )
                order by date_rec asc
            ) as value
        from hmt_temperatures where tag = :P1_TAG
            and date_rec between l_start_date and l_end_date
            and temperature_2m_max is not null
        group by trunc(date_rec, 'MONTH')
        order by trunc(date_rec, 'MONTH')
    )
    loop
        l_date_str := to_char(r.year_month, 'YYYY-MM');
        l_days := json_array_t(r.value);
        /* 日曜日から1日までの穴埋めをする */
        l_day := to_number(to_char(r.year_month,'D'));
        for i in 1..(l_day-1)
        loop
            l_days.put(0, json_object_t('{ "date": 0, "value": null }'));
        end loop;
        l_object := json_object_t();
        l_object.put('month', l_date_str);
        l_object.put('temperatures', l_days);
        l_response_json.append(l_object);
    end loop;
    l_response := l_response_json.to_clob();
    -- apex_debug.info(l_response);
    htp.p(l_response);
end;