Oracle 19c - JSON 활용 방법

• 오라클 데이터베이스는 JSON(JavaScript Object Notation)을 네이티브 형식으로 지원하며, JSON 데이터를 효과적으로 저장, 조회 및 분석할 수 있는 다양한 기능을 제공합니다.
• 특히 오라클 19c에서는 JSON 처리 기능이 더욱 강화되었으며, 기존의 관계형 데이터와 함께 활용할 수 있도록 최적화되었습니다.

오라클에서 JSON 활용 방식

1. JSON 데이터 저장 방식

1) VARCHAR2, CLOB을 이용한 JSON 저장

• 간단한 JSON 데이터를 저장할 때 적합.
• JSON 데이터 크기가 크다면 CLOB을 사용하여 저장 가능.

예제: VARCHAR2에 JSON 데이터 저장

CREATE TABLE employees_json (
    id NUMBER PRIMARY KEY,
    data CLOB CHECK (data IS JSON) -- JSON 유효성 검사 추가
);

INSERT INTO employees_json VALUES (1, '{"name": "John", "age": 30, "department": "IT"}');

 

• CHECK (data IS JSON) : 해당 컬럼이 JSON 형식인지 자동으로 검증.
  
  

2) JSON 데이터 유형(JSON Data Type)

• 오라클 21c부터는 JSON을 위한 전용 데이터 유형(JSON 데이터 타입)을 제공하지만, 오라클 19c에서는 제공되지 않음
• 따라서 19c에서는 JSON을 CLOB 또는 VARCHAR2 형식으로 저장 후 JSON 함수를 활용.

예제: JSON 형식을 적용한 테이블

CREATE TABLE orders_json (
    order_id NUMBER PRIMARY KEY,
    order_details CLOB CHECK (order_details IS JSON)
);

2. JSON 데이터 조회

오라클 19c에서는 JSON 데이터를 조회할 때 JSON_TABLE, JSON_VALUE, JSON_QUERY 등의 JSON 함수를 사용할 수 있음.

1) JSON_VALUE 함수 (단일 값 조회)

• JSON 내부에서 특정 필드 값을 추출할 때 사용.
• JSON 데이터를 SQL의 일반적인 컬럼처럼 다룰 수 있음.

예제: JSON 데이터에서 “name” 필드만 추출

SELECT id, JSON_VALUE(data, '$.name') AS employee_name
FROM employees_json;

• $ : JSON 루트 노드를 의미.
• $.name : JSON 객체에서 name 필드를 가져옴.
  
  

2) JSON_QUERY 함수 (JSON 부분 객체 조회)

• JSON 객체에서 부분 데이터를 그대로 JSON 형식으로 반환.

예제: 특정 JSON 필드(객체)를 JSON 형식 그대로 반환

SELECT JSON_QUERY(data, '$.department') AS department_json
FROM employees_json;

• 결과는 "{department: 'IT'}" 같은 JSON 형태로 반환됨.

3) JSON_TABLE 함수 (JSON 데이터를 테이블 형식으로 변환)

• JSON 데이터를 행/열 구조로 변환하여 일반 SQL 쿼리처럼 사용 가능.

예제: JSON 데이터를 테이블 형태로 변환

SELECT e.*
FROM employees_json,
JSON_TABLE(data, '$'
    COLUMNS (
        name VARCHAR2(50) PATH '$.name',
        age NUMBER PATH '$.age',
        department VARCHAR2(50) PATH '$.department'
    )
) e;

 

• JSON 내부 데이터를 테이블 형태로 변환하여 SELECT 문에서 활용 가능.

(3) JSON 데이터 수정
오라클에서는 JSON 데이터를 업데이트할 때JSON_MERGEPATCH 및 UPDATE 문을 활용할 수 있음.

1) JSON_MERGEPATCH 함수 (JSON 부분 업데이트)
• 기존 JSON 데이터에서 특정 필드만 업데이트 가능

---

관련 블로그

Oracle 19c의 주요 특징 및 12c 와의 비교