오라클 JSON 데이터 처리: JSON_TABLE과 JSON_VALUE

오라클 데이터베이스는 JSON 데이터를 네이티브로 처리할 수 있는 강력한 기능을 제공합니다.
특히, JSON_TABLE과 JSON_VALUE 함수는 JSON 데이터를 관계형 데이터베이스 형식으로 변환하거나 특정 값을 추출하는 데 유용합니다.
이 글에서는 JSON 데이터 처리의 개념, JSON_TABLE과 JSON_VALUE의 주요 사용법, 그리고 실무에서 활용할 수 있는 예제 3가지를 상세히 소개하겠습니다.

---

1. JSON 데이터 처리란?

**JSON(JavaScript Object Notation)**은 데이터를 계층 구조로 표현하는 경량 데이터 교환 형식입니다.
오라클 데이터베이스는 JSON 데이터를 저장하고, 조회하고, 변환할 수 있는 다양한 기능을 제공합니다.

---

2. JSON 데이터 처리 기본

오라클에서는 JSON 데이터를 CLOB 또는 VARCHAR2 형식으로 저장할 수 있으며, JSON 데이터를 유효성 검사 및 변환하는 다음과 같은 주요 함수와 기능을 제공합니다.
함수 설명

JSON_VALUE	JSON 문서에서 특정 값을 추출합니다.
JSON_TABLE	JSON 데이터를 테이블 형식으로 변환합니다.
IS JSON	특정 컬럼이 JSON 데이터인지 확인합니다.
JSON_QUERY	JSON 문서에서 객체나 배열 데이터를 추출합니다.

---

3. JSON_VALUE: 특정 값 추출하기

JSON_VALUE 함수는 JSON 문서에서 특정 키나 경로의 값을 추출하는 데 사용됩니다.

JSON_VALUE 기본 문법

JSON_VALUE(JSON_DATA, 'JSON 경로' RETURNING 데이터_타입 DEFAULT 기본값 ON ERROR NULL ON EMPTY NULL)

주요 옵션

• JSON_DATA: JSON 데이터가 저장된 컬럼 또는 표현식.
• JSON 경로: 추출할 값을 지정하는 경로($.key 형식).
  • $는 JSON 루트를 나타냅니다.
  • .는 객체의 키를 탐색.
  • []는 배열 요소를 탐색.
• RETURNING 데이터_타입: 반환할 데이터 유형 지정 (예: VARCHAR2, NUMBER 등).
• ON ERROR / ON EMPTY: 데이터가 없거나 오류가 발생할 때의 처리 방식.

---

JSON_VALUE 사용 예제

예제: 특정 값 추출

SELECT JSON_VALUE('{"id": 101, "name": "Alice"}', '$.name') AS NAME
FROM DUAL;

결과
NAME

Alice

예제: 기본값 사용

SELECT JSON_VALUE('{"id": 101}', '$.name' DEFAULT 'Unknown' ON EMPTY RETURNING VARCHAR2(100)) AS NAME
FROM DUAL;

결과
NAME

Unknown

---

4. JSON_TABLE: 테이블 형식으로 변환하기

JSON_TABLE 함수는 JSON 데이터를 테이블 형식으로 변환하여 쿼리할 수 있도록 합니다.

JSON_TABLE 기본 문법

JSON_TABLE(
    JSON_DATA, 
    'JSON 경로' 
    COLUMNS (
        컬럼명 데이터_타입 PATH 'JSON 경로',
        ...
    )
)

주요 구성 요소

• JSON_DATA: JSON 데이터가 저장된 컬럼 또는 표현식.
• 'JSON 경로': JSON 데이터의 최상위 노드를 지정.
• COLUMNS: JSON 데이터에서 추출할 컬럼을 정의.
  • PATH: 각 컬럼에 해당하는 JSON 키 또는 경로를 지정.
  • 데이터_타입: 반환할 데이터 유형 지정.

---

JSON_TABLE 사용 예제

예제: JSON 배열을 테이블로 변환

JSON 데이터:

[
  {"id": 1, "name": "Alice", "age": 30},
  {"id": 2, "name": "Bob", "age": 25}
]

SQL 문:

SELECT *
FROM JSON_TABLE(
    '[{"id": 1, "name": "Alice", "age": 30}, {"id": 2, "name": "Bob", "age": 25}]',
    '$[*]'
    COLUMNS (
        ID NUMBER PATH '$.id',
        NAME VARCHAR2(100) PATH '$.name',
        AGE NUMBER PATH '$.age'
    )
);

결과
ID NAME AGE

1	Alice	30
2	Bob	25

---

예제: JSON 객체에서 데이터 추출

JSON 데이터:

{"employees": [{"id": 1, "name": "Alice"}, {"id": 2, "name": "Bob"}]}

SQL 문:

SELECT *
FROM JSON_TABLE(
    '{"employees": [{"id": 1, "name": "Alice"}, {"id": 2, "name": "Bob"}]}',
    '$.employees[*]'
    COLUMNS (
        ID NUMBER PATH '$.id',
        NAME VARCHAR2(100) PATH '$.name'
    )
);

결과
ID NAME

1	Alice
2	Bob

---

5. 실무에서의 활용 예제

예제 1: JSON 데이터에서 특정 키 값 추출

요구사항
고객 정보가 JSON 형식으로 저장된 CUSTOMER_DATA 테이블에서 이름(name)과 나이(age)를 추출합니다.
SQL 문

SELECT 
    JSON_VALUE(CUSTOMER_INFO, '$.name') AS NAME,
    JSON_VALUE(CUSTOMER_INFO, '$.age' RETURNING NUMBER) AS AGE
FROM CUSTOMER_DATA;

---

예제 2: JSON 배열을 테이블로 변환하여 분석

요구사항
ORDERS 테이블의 JSON 형식 주문 데이터에서 제품별 정보를 테이블로 변환합니다.
SQL 문

SELECT *
FROM ORDERS O, 
     JSON_TABLE(
        O.ORDER_DETAILS,
        '$.products[*]'
        COLUMNS (
            PRODUCT_ID NUMBER PATH '$.id',
            PRODUCT_NAME VARCHAR2(100) PATH '$.name',
            QUANTITY NUMBER PATH '$.quantity'
        )
    ) PRODUCTS;

---

예제 3: JSON 데이터와 관계형 데이터 결합

요구사항
JSON 데이터에서 추출한 사용자 ID를 기반으로 USERS 테이블과 결합합니다.
SQL 문

SELECT U.USER_NAME, J.ORDER_ID, J.TOTAL_AMOUNT
FROM USERS U
JOIN JSON_TABLE(
    '[{"user_id": 1, "order_id": 1001, "total_amount": 500.0}]',
    '$[*]'
    COLUMNS (
        USER_ID NUMBER PATH '$.user_id',
        ORDER_ID NUMBER PATH '$.order_id',
        TOTAL_AMOUNT NUMBER PATH '$.total_amount'
    )
) J
ON U.USER_ID = J.USER_ID;

---

6. JSON 처리 시 주의사항

1. JSON 데이터 유효성 검사
   • JSON 데이터를 삽입하기 전에 IS JSON을 사용하여 데이터의 유효성을 확인하세요.
2. 데이터 타입
   • JSON_VALUE와 JSON_TABLE의 데이터 타입은 반환값과 일치하도록 명시하세요. 불일치 시 오류가 발생할 수 있습니다.
3. 성능 최적화
   • JSON 데이터의 구조가 복잡할수록 성능 저하가 발생할 수 있습니다. 필요한 경로만 명확히 지정하세요.

---

7. 결론

Oracle Database에서 JSON_VALUE와 JSON_TABLE을 사용하면 JSON 데이터를 관계형 데이터로 효율적으로 변환하거나 필요한 값을 추출할 수 있습니다.
이 두 가지 기능을 활용하여 JSON 데이터 처리의 복잡성을 줄이고, 데이터 분석 및 통합 작업을 간소화하세요.
이제 오라클 JSON 처리 기능을 사용해 실무 데이터를 더 효율적으로 다뤄보세요! 😊