[DB]MySQL과 Oracle의 JSON🎉

현대 웹 및 모바일 애플리케이션 개발에서 JSON(JavaScript Object Notation)은 필수불가결한 데이터 포맷입니다. API 통신, 설정 파일, 로그 데이터 등 다양한 영역에서 JSON이 활용됨에 따라, 관계형 데이터베이스에서도 JSON 데이터를 효율적으로 처리하는 능력이 중요해졌습니다. 본 가이드에서는 MySQL과 Oracle에서 JSON 데이터를 어떻게 다루는지 비교 분석하고, 실제 개발 시나리오에 적용 가능한 예제를 통해 이해를 돕고자 합니다. 🚀

---

1. JSON, 왜 쓰는 걸까요? (Why JSON?) 🔧

JSON(JavaScript Object Notation)은 사람이 읽고 쓰기 쉬우면서, 기계가 파싱하고 생성하기에도 용이한 데이터 교환 형식입니다. 가볍고 구조적인 텍스트 기반 데이터 포맷으로, 다음과 같은 특징을 가집니다.

• 간결하고 명확한 데이터 표현: 키-값 쌍으로 구성되어 데이터를 직관적으로 표현할 수 있습니다.
• 유연한 데이터 구조: 배열과 중첩 구조를 지원하여 복잡한 데이터도 효과적으로 나타낼 수 있습니다.
• 광범위한 호환성: JavaScript를 비롯한 다양한 프로그래밍 언어와 플랫폼에서 쉽게 사용할 수 있습니다.

{
  "name": "앨리스",
  "age": 30,
  "hobbies": ["독서", "사이클링"],
  "address": {
    "city": "서울",
    "zipcode": "04524"
  }
}

---

2. MySQL에서의 JSON 활용 (JSON in MySQL) 🌐

MySQL 5.7부터는 JSON 데이터 타입을 네이티브하게 지원하여, JSON 데이터를 효율적으로 저장, 쿼리, 조작할 수 있습니다.

• JSON 컬럼 정의: JSON 데이터 타입을 사용하여 테이블 스키마를 정의합니다.

CREATE TABLE Users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    userInfo JSON
);

• JSON 데이터 삽입: JSON 문자열을 직접 삽입하거나, JSON_OBJECT 함수를 사용하여 생성할 수 있습니다.

INSERT INTO Users (userInfo) VALUES
('{"name": "밥", "age": 25, "hobbies": ["게임", "등산"]}');

-- JSON_OBJECT 함수 사용
INSERT INTO Users (userInfo) VALUES (JSON_OBJECT('name', '철수', 'age', 28));

• JSON 데이터 쿼리: JSON_EXTRACT 함수 또는 ->> 연산자를 사용하여 JSON 필드에서 특정 값을 추출합니다.

SELECT JSON_EXTRACT(userInfo, '$.name') AS name FROM Users;
-- 또는
SELECT userInfo->>'$.name' AS name FROM Users; -- 더 간결한 표현

-- 중첩된 JSON 데이터 접근
SELECT userInfo->>'$.address.city' AS city FROM Users;

• JSON 데이터 업데이트: JSON_SET, JSON_INSERT, JSON_REPLACE, JSON_REMOVE 등의 함수를 사용하여 JSON 데이터를 수정합니다.

-- age 필드 업데이트
UPDATE Users SET userInfo = JSON_SET(userInfo, '$.age', 26) WHERE id = 1;

-- 새로운 필드 추가
UPDATE Users SET userInfo = JSON_INSERT(userInfo, '$.email', 'bob@example.com') WHERE id = 1;

-- hobbies 배열에 새로운 항목 추가
UPDATE Users SET userInfo = JSON_ARRAY_APPEND(userInfo, '$.hobbies', 'coding') WHERE id = 1;

• JSON 함수 활용: JSON_CONTAINS, JSON_CONTAINS_PATH, JSON_VALID 등 다양한 내장 함수를 활용하여 JSON 데이터를 효과적으로 다룰 수 있습니다.

---

3. Oracle에서 JSON 데이터 처리 🌱

Oracle 12c Release 2부터 JSON을 본격적으로 지원하며, 강력한 JSON 처리 기능을 제공합니다.

• JSON 데이터 저장: VARCHAR2, CLOB, BLOB 타입의 컬럼에 JSON 데이터를 저장하며, IS JSON 제약 조건을 사용하여 데이터의 유효성을 검사합니다. 일반적으로 CLOB 타입을 권장합니다.

CREATE TABLE Employees (
    id NUMBER PRIMARY KEY,
    details CLOB CONSTRAINT ensure_json CHECK (details IS JSON)
);

• JSON 데이터 쿼리: JSON_TABLE 함수를 사용하여 JSON 데이터를 관계형 테이블 형태로 변환하여 쿼리합니다.

SELECT jt.*
FROM Employees,
     JSON_TABLE(details, '$'
       COLUMNS (name VARCHAR2(50) PATH '$.name',
                position VARCHAR2(50) PATH '$.position',
                city VARCHAR2(50) PATH '$.address.city')
     ) jt
WHERE id = 1;

-- Oracle 19c 이상에서는 더욱 간결하게 사용 가능
SELECT jt.*
FROM JSON_TABLE(Employees.details, '$'
       COLUMNS (name VARCHAR2(50) PATH '$.name',
                position VARCHAR2(50) PATH '$.position')
     ) jt;

-- JSON_VALUE를 사용하여 직접 값 추출
SELECT JSON_VALUE(details, '$.name') AS name FROM Employees;

• JSON 데이터 업데이트: JSON_MERGEPATCH 함수를 사용하여 JSON 데이터를 부분적으로 업데이트합니다.

UPDATE Employees
SET details = JSON_MERGEPATCH(details, '{"position": "선임 매니저", "address": {"city": "부산"}}')
WHERE id = 1;

---

반응형

4. MySQL vs Oracle JSON 기능 비교 🌎

기능	MySQL	Oracle
JSON 데이터 타입 지원	✅ 네이티브 JSON 타입. 저장 및 쿼리 성능 향상.	❌ 문자열(주로 CLOB)로 저장. IS JSON 제약 조건으로 유효성 검사.
JSON 쿼리	JSON_EXTRACT, ->>, JSON_CONTAINS 등 비교적 직관적인 함수 제공.	JSON_TABLE을 사용하여 관계형 데이터로 변환 후 쿼리. JSON_VALUE를 통한 직접 추출도 가능. 더 강력하고 유연한 쿼리 기능 제공.
JSON 함수	JSON_SET, JSON_INSERT, JSON_ARRAY_APPEND 등 다양한 함수 제공.	JSON_MERGEPATCH를 포함한 다양한 함수 제공. JSON 데이터의 복잡한 변환 및 조작에 유용.
성능	간단한 JSON 쿼리 및 웹 애플리케이션에 적합.	대용량 데이터 처리 및 복잡한 JSON 쿼리에 더욱 효율적일 수 있음. 특히, 데이터 웨어하우징 및 분석 환경에서 강점.
개발 편의성	비교적 배우기 쉽고 사용하기 간편함.	JSON_TABLE 등 고급 기능을 이해해야 효과적으로 활용 가능. 하지만, 더 강력한 기능을 제공.

---

5. 실습: JSON 데이터 분석 📊

JSON 데이터로 직원 정보를 저장하고, 특정 직원의 이름과 직위를 추출합니다.

MySQL:

SELECT
    JSON_EXTRACT(details, '$.name') AS name,
    JSON_EXTRACT(details, '$.position') AS position
FROM Employees;

 

Oracle:

SELECT *
FROM JSON_TABLE(
    details,
    '$' COLUMNS (
        name VARCHAR2(100) PATH '$.name',
        position VARCHAR2(100) PATH '$.position'
    )
);

---

6. 마무리 및 활용 방안 🌟

JSON은 현대 애플리케이션 데이터 구조에서 필수적인 역할을 합니다. MySQL과 Oracle 모두 JSON 데이터를 지원하지만, 각 데이터베이스의 장단점을 이해하고 적절히 활용하는 것이 중요합니다.

• MySQL: 간단한 JSON 데이터 처리 및 네이티브 데이터 타입을 선호한다면 적합.
• Oracle: 복잡한 JSON 데이터 분석 및 강력한 변환 기능이 필요하다면 적합.

MySQL과 Oracle은 각각의 장단점을 가진 JSON 처리 기능을 제공합니다. 프로젝트의 요구 사항, 데이터 규모, 개발팀의 숙련도 등을 고려하여 적절한 데이터베이스를 선택하는 것이 중요합니다. 이 가이드가 개발자들이 JSON 데이터를 효율적으로 활용하는 데 도움이 되기를 바랍니다. 😊