오라클 JSON_OBJECTAGG 함수 완전 정복 – JSON 객체로 데이터를 변환하는 실전 예제 총정리

 

오라클 SQL에서 JSON 데이터를 다뤄야 하는 경우, 특히 여러 행의 데이터를 하나의 JSON 객체로 병합하고 싶다면 JSON_OBJECTAGG 함수만큼 강력한 도구는 없습니다. 이 포스팅에서는 JSON_OBJECTAGG 함수의 기본 사용법부터 고급 옵션, 다양한 실무 활용 사례까지 완벽하게 정리해 드리겠습니다.

---

🔍 JSON_OBJECTAGG 함수란?

JSON_OBJECTAGG는 오라클 12c Release 2(12.2) 이상 버전에서 제공되는 JSON 함수로, 여러 행의 데이터를 KEY: VALUE 형태로 하나의 JSON 객체로 병합할 수 있는 집계 함수입니다. GROUP BY 절과 함께 사용하면 그룹별로 JSON 객체를 만들 수 있으며, 옵션을 활용하면 반환 형식과 NULL 처리 방식까지 세세하게 조정 가능합니다.

---

🧱 1. 기본 사용법

✅ 기본 구조

SELECT JSON_OBJECTAGG(KEY 컬럼1 VALUE 컬럼2) FROM 테이블;

• KEY: JSON의 키로 사용할 값
• VALUE: JSON의 값으로 사용할 컬럼
• 하나의 JSON 객체에 여러 행을 병합 가능

📌 예제 1: 이름을 키, 급여를 값으로

WITH emp AS (
    SELECT 7788 empno, 'SCOTT' ename, 'ANALYST' job, 7566 sal FROM dual UNION ALL
    SELECT 7902 empno, 'FORD'  ename, 'ANALYST' job, 7566 sal FROM dual
)
SELECT JSON_OBJECTAGG(KEY ename VALUE sal) AS json_data
  FROM emp;

🧾 결과:

{"SCOTT":7566,"FORD":7566}

간단하게 ENAME: SAL 구조의 JSON 객체를 만들 수 있습니다.

---

🧩 2. GROUP BY와 함께 사용하기

JSON_OBJECTAGG는 그룹 함수이므로 GROUP BY절과 함께 사용하여 그룹별 JSON 객체를 생성할 수 있습니다.

📌 예제 2: 직무별로 이름과 급여 정보를 JSON으로 그룹화

WITH emp AS (
    SELECT 7788 empno, 'SCOTT' ename, 'ANALYST' job, 7566 sal FROM dual UNION ALL
    SELECT 7902 empno, 'FORD'  ename, 'ANALYST' job, 7566 sal FROM dual UNION ALL
    SELECT 7698 empno, 'BLAKE' ename, 'MANAGER' job, 2850 sal FROM dual
)
SELECT job, JSON_OBJECTAGG(KEY ename VALUE sal) AS json_data
  FROM emp
GROUP BY job;

🧾 결과:

ANALYST: {"SCOTT":7566,"FORD":7566}
MANAGER: {"BLAKE":2850}

직무(job) 기준으로 그룹핑하여 각 그룹에 대한 JSON 객체를 생성합니다.

---

🏗️ 3. VALUE에 JSON_OBJECT를 중첩하여 사용

VALUE 절에는 단순한 값뿐만 아니라 JSON_OBJECT() 함수를 넣어 다층 구조의 JSON을 만들 수 있습니다.

📌 예제 3: 사번을 키로, 이름/직무/급여를 값으로 구성

WITH emp AS (
    SELECT 7788 empno, 'SCOTT' ename, 'ANALYST' job, 7566 sal FROM dual UNION ALL
    SELECT 7902 empno, 'FORD' ename, 'ANALYST' job, 7566 sal FROM dual
)
SELECT JSON_OBJECTAGG(
         KEY TO_CHAR(empno)
         VALUE JSON_OBJECT(
                 KEY 'ENAME' VALUE ename,
                 KEY 'JOB'   VALUE job,
                 KEY 'SAL'   VALUE sal
              )
       ) AS json_data
  FROM emp;

🧾 결과:

{
  "7788":{"ENAME":"SCOTT","JOB":"ANALYST","SAL":7566},
  "7902":{"ENAME":"FORD","JOB":"ANALYST","SAL":7566}
}

VALUE에 JSON 객체를 넣으면 훨씬 더 구조화된 데이터를 생성할 수 있습니다.

---

⚙️ 4. FORMAT JSON 옵션

만약 VALUE 값이 이미 JSON 문자열이라면, FORMAT JSON 옵션을 추가해야 문자열이 아닌 진짜 JSON 객체로 인식됩니다.

📌 예제 4: VALUE가 JSON 문자열일 때

WITH emp AS (
    SELECT 7788 empno, '{"ENAME":"SCOTT"}' info_json FROM dual UNION ALL
    SELECT 7902 empno, '{"ENAME":"FORD"}' info_json FROM dual
)
SELECT
    JSON_OBJECTAGG(KEY TO_CHAR(empno) VALUE info_json) AS result1,
    JSON_OBJECTAGG(KEY TO_CHAR(empno) VALUE info_json FORMAT JSON) AS result2
  FROM emp;

🧾 차이점:

• result1: 문자열 그대로 반환
• result2: 실제 JSON 객체로 반환

---

🧽 5. NULL 처리 방식 – NULL ON NULL / ABSENT ON NULL

오라클 19c 이상에서만 사용 가능한 옵션입니다.

📌 예제 5: sal이 NULL인 경우 처리 방식

WITH emp AS (
    SELECT 'SCOTT' ename, 7566 sal FROM dual UNION ALL
    SELECT 'JONES' ename, NULL sal FROM dual
)
SELECT
    JSON_OBJECTAGG(KEY ename VALUE sal) AS default_result,
    JSON_OBJECTAGG(KEY ename VALUE sal NULL ON NULL) AS include_null,
    JSON_OBJECTAGG(KEY ename VALUE sal ABSENT ON NULL) AS exclude_null
  FROM emp;

🧾 결과:

• default_result: 오라클 19c 이상에서 NULL 포함
• include_null: "JONES": null
• exclude_null: "JONES" 항목 자체가 생략됨

NULL 처리 방식은 JSON 결과의 정확성과 활용에 큰 영향을 줄 수 있습니다.

---

🧾 6. RETURNING 옵션 – 반환 데이터 타입 지정

JSON_OBJECTAGG의 기본 반환 타입은 VARCHAR2(4000)입니다. 이보다 더 큰 JSON 객체를 반환하고자 할 때는 CLOB 또는 BLOB 타입을 지정할 수 있습니다.

📌 예제 6: 반환 타입 지정

SELECT
    JSON_OBJECTAGG(KEY ename VALUE sal) AS default_result,
    JSON_OBJECTAGG(KEY ename VALUE sal RETURNING VARCHAR2(4000)) AS varchar_result,
    JSON_OBJECTAGG(KEY ename VALUE sal RETURNING CLOB) AS clob_result,
    JSON_OBJECTAGG(KEY ename VALUE sal RETURNING BLOB) AS blob_result
  FROM emp;

🧾 주의사항:

• CLOB: 텍스트가 아주 큰 경우에 사용
• BLOB: 이진 데이터 형식으로 API 응답 등에 활용

---

💡 실무 팁 정리

기능 설명

GROUP BY 사용	그룹별 JSON 객체 생성
중첩 JSON	VALUE에 JSON_OBJECT() 사용
FORMAT JSON	JSON 문자열을 JSON 객체로 인식
NULL ON NULL, ABSENT ON NULL	NULL 처리 방식 선택
RETURNING CLOB	긴 JSON 객체 반환 시 유용

---

📚 마무리

JSON_OBJECTAGG 함수는 단순한 데이터 병합을 넘어, 구조화된 JSON 데이터 생성이라는 강력한 기능을 제공합니다. 오라클 기반 시스템에서 REST API, 프론트엔드와의 연동, 데이터 포맷 전환 등 다양한 업무에서 활용할 수 있습니다.

정형화된 SQL을 넘어 JSON 기반의 현대적 데이터 구조로 전환하고 싶다면, 지금 당장 JSON_OBJECTAGG 함수로 시작해보세요!