oracle json 필드 활용

오라클 12c 버전부터 본격적으로 지원되기 시작한 Oracle JSON 기능은 정형 데이터(SQL)와 비정형 데이터(JSON)의 장점을 결합한 강력한 도구입니다. k쇼핑몰 정산시스템 프로젝트에서 경험했듯이, JSON 필드에서 직접 추출해 처리할 때 유용할 수 있습니다.

1. JSON 필드 생성 및 제약 조건 설정

오라클 12.1 이후부터는 별도의 데이터 타입 대신 VARCHAR2, CLOB, BLOB을 사용하되, IS JSON 제약 조건을 걸어 데이터의 무결성을 보장합니다.

CREATE TABLE api_log (
    log_id        NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    -- JSON 데이터를 담는 필드 (최신 버전은 JSON 타입을 직접 사용 가능)
    api_json   CLOB, 
    -- 저장 시 JSON 형식이 맞는지 검증하는 제약 조건
    CONSTRAINT ensure_json CHECK (pg_api_json IS JSON)
);

• 참고: 오라클 21c부터는 전용 데이터 타입인 JSON 타입이 추가되어 성능과 저장 효율이 더욱 향상되었습니다.

2. JSON 데이터 입력 (Insert)

JSON 필드에는 일반적인 문자열 형태 그대로 데이터를 넣으면 됩니다. 제약 조건이 있다면 잘못된 형식(예: 닫는 중괄호 누락) 입력 시 오류가 발생합니다.

INSERT INTO api_log (pg_api_json) 
VALUES ( '{"settleType": "정산", "paySettleAmount": 50000, "productOrderId": "P12345"}');

3. JSON 데이터 활용 (Query)

자주 사용하는 3가지 함수를 익히면 대부분의 작업이 가능합니다.

1) JSON_VALUE: 단일 값 추출
하나의 필드에서 특정 값을 꺼낼 때 사용합니다. 숫자로 계산이 필요하다면 RETURNING NUMBER를 명시하는 것이 중요합니다.

사용 예: JSON_VALUE(필드명, '$.경로' RETURNING 타입)

2) JSON_QUERY: 객체나 배열 추출
단일 값이 아니라 JSON 내의 특정 오브젝트({})나 배열([]) 전체를 그대로 가져올 때 사용합니다.

3) JSON_TABLE: JSON을 관계형 테이블처럼 변환
가장 강력한 기능입니다. JSON 내의 배열 데이터를 일반적인 JOIN이 가능한 로우(Row) 형태로 풀어서 보여줍니다.

SELECT t.*
FROM api_log l,
     JSON_TABLE(l.pg_api_json, '$'
         COLUMNS (
             settle_type   VARCHAR2(20) PATH '$.settleType',
             pay_amt       NUMBER       PATH '$.paySettleAmount',
             order_id      VARCHAR2(50) PATH '$.productOrderId'
         )
     ) t;

• 성능 최적화 팁 (Index)
  JSON 필드 조회 속도가 느려진다면, 함수 기반 인덱스(Function-Based Index)를 사용하여 특정 JSON 경로의 값에 인덱스를 걸 수 있습니다.

-- settleType을 자주 조건으로 쓴다면
CREATE INDEX idx_pg_json_settle_type 
ON pg_api_log (JSON_VALUE(pg_api_json, '$.settleType'));

이렇게 인덱스를 걸어두면 대용량 정산 로그 데이터에서도 CASE WHEN 조건 절의 성능을 비약적으로 높일 수 있습니다.