오라클 JSON_OBJECT 함수 완벽 가이드: 기본 문법부터 고급 옵션까지

 

오라클(Oracle) 데이터베이스는 다양한 JSON 관련 기능을 제공하며, 그중에서도 JSON_OBJECT 함수는 SQL을 통해 JSON 객체를 직접 생성할 수 있게 해주는 매우 강력한 도구입니다. 이 글에서는 오라클 12c R2 이상 버전에서 사용할 수 있는 JSON_OBJECT 함수의 기본 사용법부터 다양한 고급 옵션들까지 예제를 통해 상세하게 설명드립니다. 실무에서 JSON 데이터를 처리해야 하는 개발자나 데이터베이스 관리자(DBA) 분들께 특히 유익한 내용입니다.

---

✅ JSON_OBJECT 함수란?

JSON_OBJECT 함수는 SQL 쿼리 내에서 JSON 객체를 생성할 수 있도록 도와주는 함수입니다. 이 함수를 사용하면 테이블의 컬럼을 JSON 형식으로 출력하거나, 복잡한 JSON 구조를 중첩 형태로 생성할 수 있습니다. 오라클 12c R2 이상부터 지원되며, 버전에 따라 일부 기능(예: * 사용 또는 WITH UNIQUE KEYS)은 제한될 수 있습니다.

---

📌 기본 문법

JSON_OBJECT(
  [
    KEY key_expr VALUE val_expr 
    | expr [":" expr] 
  ] [FORMAT JSON],
  ...
  [ JSON_on_null_clause ]
  [ JSON_returning_clause ]
  [ STRICT ]
  [ WITH UNIQUE KEYS ]
)

• KEY와 VALUE를 통해 JSON의 "키:값" 쌍을 구성
• expr : expr 문법도 가능
• FORMAT JSON: JSON 문자열을 구조체로 인식
• JSON_on_null_clause: NULL 처리 방식 설정
• RETURNING: 반환 타입 지정
• STRICT: JSON 유효성 검사 수행
• WITH UNIQUE KEYS: 키 중복 방지 (Oracle 21c 이상)

---

🧪 기본 사용 예제

🎯 간단한 JSON 객체 생성

SELECT JSON_OBJECT(
           KEY 'EMPNO' VALUE 7698,
           KEY 'ENAME' VALUE 'BLAKE',
           KEY 'JOB'   VALUE 'MANAGER'
       ) AS json_data
  FROM dual;

출력 결과:

{"EMPNO":7698,"ENAME":"BLAKE","JOB":"MANAGER"}

키(KEY)와 값(VALUE)를 지정하여 간단하게 JSON 객체를 생성할 수 있습니다.

---

🎯 JSON 중첩 객체 생성

SELECT JSON_OBJECT(
           KEY 'EMPNO' VALUE 7698,
           KEY 'ENAME' VALUE 'BLAKE',
           KEY 'JOB'   VALUE 'MANAGER',
           KEY 'DEPT'  VALUE JSON_OBJECT(
                               KEY 'DEPTNO' VALUE 30,
                               KEY 'DNAME'  VALUE 'SALES'
                           )
       ) AS json_data
  FROM dual;

출력 결과:

{
  "EMPNO": 7698,
  "ENAME": "BLAKE",
  "JOB": "MANAGER",
  "DEPT": {
    "DEPTNO": 30,
    "DNAME": "SALES"
  }
}

이처럼 JSON_OBJECT는 중첩으로도 사용 가능하여 복잡한 JSON 구조를 표현할 수 있습니다.

---

📊 테이블 데이터로 JSON 객체 만들기

🎯 다중 행에서 JSON 객체 생성

WITH emp AS (
    SELECT 7698 empno, 'BLAKE' ename, 'MANAGER' job FROM dual UNION ALL
    SELECT 7782 empno, 'CLARK' ename, 'MANAGER' job FROM dual UNION ALL
    SELECT 7566 empno, 'JONES' ename, 'MANAGER' job FROM dual
)

SELECT JSON_OBJECT(
           KEY 'EMPNO' VALUE empno,
           KEY 'ENAME' VALUE ename,
           KEY 'JOB'   VALUE job
       ) AS json_data
  FROM emp;

각 행마다 하나의 JSON 객체가 생성되며, JSON 형식으로 직관적으로 출력됩니다.

---

🎯 모든 컬럼을 JSON으로 표현 (Oracle 19c 이상)

SELECT JSON_OBJECT(*) AS json_data FROM emp;

Oracle 19c부터는 * 사용이 가능하여, 테이블의 모든 컬럼을 자동으로 JSON 객체로 변환할 수 있습니다.

---

⚙️ JSON_OBJECT 함수의 다양한 옵션 사용법

📍 FORMAT JSON / STRICT 옵션

SELECT JSON_OBJECT(KEY 'EMP' VALUE '{"ENAME":"BLAKE"}')                    AS res1,
       JSON_OBJECT(KEY 'EMP' VALUE '{"ENAME":"BLAKE"}' FORMAT JSON)        AS res2,
       JSON_OBJECT(KEY 'EMP' VALUE '{"ENAME":"BLAKE"}' FORMAT JSON STRICT) AS res3
  FROM dual;

• FORMAT JSON: 문자열을 JSON 객체로 인식
• STRICT: 유효한 JSON 형식이 아닐 경우 오류 발생

---

📍 NULL ON NULL / ABSENT ON NULL

SELECT JSON_OBJECT(
           KEY 'ENAME' VALUE 'BLAKE', 
           KEY 'SAL' VALUE NULL
       ) AS result1,
       JSON_OBJECT(
           KEY 'ENAME' VALUE 'BLAKE', 
           KEY 'SAL' VALUE NULL NULL ON NULL
       ) AS result2,
       JSON_OBJECT(
           KEY 'ENAME' VALUE 'BLAKE', 
           KEY 'SAL' VALUE NULL ABSENT ON NULL
       ) AS result3
  FROM dual;

• 기본 동작은 Oracle 18c까지는 NULL 값을 생략(ABSENT), 19c부터는 포함(NULL ON NULL)
• 명시적으로 NULL ON NULL 또는 ABSENT ON NULL을 지정 가능

---

📍 RETURNING 옵션

SELECT JSON_OBJECT(
           KEY 'EMPNO' VALUE 7698,
           KEY 'ENAME' VALUE 'BLAKE'
           RETURNING VARCHAR2(4000)
       ) AS result1,
       JSON_OBJECT(
           KEY 'EMPNO' VALUE 7698,
           KEY 'ENAME' VALUE 'BLAKE'
           RETURNING CLOB
       ) AS result2,
       JSON_OBJECT(
           KEY 'EMPNO' VALUE 7698,
           KEY 'ENAME' VALUE 'BLAKE'
           RETURNING BLOB
       ) AS result3
  FROM dual;

• 기본 반환 타입은 VARCHAR2(4000)
• 더 큰 JSON 결과를 다루려면 CLOB, BLOB 사용

---

🔐 WITH UNIQUE KEYS (Oracle 21c 이상)

이 옵션은 JSON 객체에서 중복된 키가 생성되는 것을 방지합니다. 중복 키가 있을 경우 오류가 발생합니다.

SELECT JSON_OBJECT(
           KEY 'ID' VALUE 1,
           KEY 'ID' VALUE 2 WITH UNIQUE KEYS
       ) FROM dual;
-- ORA-40578: JSON_OBJECT: duplicate key 'ID'

Oracle 21c부터 지원되므로, 하위 버전에서는 사용할 수 없습니다.

---

🚀 마무리: 실무에 바로 적용 가능한 JSON_OBJECT

오라클에서 JSON 데이터를 다룰 때 JSON_OBJECT 함수는 정말로 유용합니다. 테이블의 데이터를 REST API 형태로 전달하거나, 클라이언트에서 JSON 구조로 필요한 데이터를 바로 받아야 하는 상황이라면 JSON_OBJECT 함수를 적극적으로 활용해 보세요.

특히 유용한 활용 사례:

• 백엔드 시스템에서 JSON 형태로 API 응답을 구성할 때
• JSON 데이터 로깅 및 추적
• APEX 등 오라클 기반 웹 애플리케이션에서 JSON 객체 전달

---

📚 요약

기능 설명

기본 문법	KEY와 VALUE 조합 또는 expr : expr 형태
중첩 사용	JSON_OBJECT 안에 다시 JSON_OBJECT
NULL 처리	NULL ON NULL, ABSENT ON NULL 옵션으로 제어
JSON 형식 검증	FORMAT JSON, STRICT 옵션 사용
반환 타입 제어	RETURNING으로 VARCHAR2, CLOB, BLOB 지정
키 중복 방지	WITH UNIQUE KEYS (Oracle 21c 이상)

---

👉 Tip: Oracle JSON 함수는 JSON_OBJECT 외에도 JSON_ARRAY, JSON_TABLE, JSON_VALUE, JSON_QUERY 등 다양한 함수군으로 구성되어 있으며, 이들과 함께 활용하면 더 강력한 JSON 처리 로직을 구성할 수 있습니다.

이제 JSON_OBJECT 함수를 실무 프로젝트에서 바로 활용해 보세요!