[{구간ID:1, 시작일:1, 종료일:15, 주기유형코드:3, 주기기간:0, 구간정산일:99}, // 당월 말일 정산
{구간ID:2, 시작일:16, 종료일:99, 주기유형코드:3, 주기기간:1, 구간정산일:20} // 다음달 20일 정산 ]
SELECT ff.cid, ff.cpid, ff.pymt_cd, ff.aply_st_ymd
, (rai->>'sn')::NUMBER AS sn
, (rai->>'payStDay')::NUMBER AS payStDay
, (rai->>'payFnshDay')::NUMBER AS payFnshDay
, (rai->>'adjItvlTypCd')::NUMBER AS adjItvlTypCd
, (rai->>'adjItvlTrm')::NUMBER AS adjItvlTrm
, (rai->>'adjItvlDay')::NUMBER AS adjItvlDay
FROM sppdb.tb_cid_gnr_adj_fee ff
CROSS JOIN json_array_elements(ff.rng_adj_info) AS rai
WHERE rng_adj_info IS NOT NULL AND aply_st_ymd = '2024-11-01';
cid cpid pymt_cd aply_st_ymd sn paystday payfnshday adjitvltypcd adjitvltrm adjitvlday
honda00001 CMSCPID cmsbank 2024-11-01 1 1 15 3 0 99
honda00001 CMSCPID cmsbank 2024-11-01 2 16 99 3 1 20
----------------
JSON ARRY사용법
SELECT a.ymd, a.cid, a.pymt_cd, a.cpid, a.adj_itvl_typ_cd, a.adj_itvl_trm
, (b->>'payStDay')::smallint pay_st_day
, (b->>'payFnshDay')::smallint pay_fnsh_day
, (b->>'adjItvlTypCd')::smallint json_adj_itvl_typ_cd
, (b->>'adjItvlTrm')::smallint json_adj_itvl_trm
, (b->>'adjItvlDay')::smallint json_adj_itvl_day
, a.rng_adj_info::varchar rng_adj_info
FROM spsdb.tb_clnt_pymt_bydd_cntrc_info a
LEFT
JOIN json_array_elements(a.rng_adj_info) b ON EXTRACT(DAY FROM '20241113'::date) BETWEEN (b->>'payStDay')::smallint AND (b->>'payFnshDay')::smallint
WHERE a.ymd = CURRENT_DATE - 1
AND a.pymt_cd = 'cmsbank'
