MySQL JSON 함수 사용해보기

MySQL JSON 타입이 생긴 이유와 사용하면 좋은 상황

MySQL은 전통적으로 관계형 데이터베이스(RDBMS)로, 엄격한 스키마(테이블 구조)를 요구했습니다. 그러나 현대 애플리케이션은 점점 더 유연한 데이터 구조를 요구하게 되었고, 다양한 형태의 데이터를 저장할 수 있는 NoSQL 데이터베이스가 주목받기 시작했습니다. 이 흐름에 대응하기 위해 MySQL은 5.7 버전부터 JSON 타입을 도입했습니다.

MySQL JSON 타입의 등장 이유:

• 스키마가 고정되지 않은 복잡한 데이터를 저장할 수 있도록 지원
• 관계형 데이터베이스의 강점을 유지하면서도 일부 NoSQL 기능 제공
• 다양한 API, IoT, 모바일 애플리케이션에서 생성되는 JSON 데이터를 자연스럽게 저장하고 조회 가능

JSON 타입을 사용하면 좋은 상황:

• 일부 필드 구조가 고정되어 있지 않고 유동적인 경우
• 검색보다는 저장과 조회가 중심이 되는 경우
• 다양한 속성(attribute)을 가진 객체들을 하나의 칼럼에 저장해야 할 때
• 복잡한 관계형 모델링 없이 데이터를 빠르게 저장하고 관리하고 싶을 때

MySQL의 JSON 타입은 유연성과 관계형 데이터베이스의 장점을 모두 살리고 싶을 때 매우 유용한 선택지가 됩니다.

이 문서에서는 "MySQL JSON 함수"를 통해 구조화된 데이터에서 특정 값을 검색과 수정, 획득하는 방법을 알아봅니다.

---

주요 MySQL JSON 함수와 사용법

1. JSON_EXTRACT

JSON 데이터에서 특정 경로(Path)에 해당하는 값을 추출할 때 사용합니다.

SELECT JSON_EXTRACT('{"user": {"name": "Alice", "age": 30}}', '$.user.name') AS username;

결과: "Alice"

2. JSON_UNQUOTE

JSON 형식으로 추출된 문자열 값에서 따옴표를 제거합니다.

SELECT JSON_UNQUOTE(JSON_EXTRACT('{"user": {"name": "Alice"}}', '$.user.name')) AS username;

결과: Alice

3. JSON_SET

기존 JSON 오브젝트에 키-값 쌍을 추가하거나 값을 업데이트합니다.

SELECT JSON_SET('{"name": "Alice"}', '$.age', 30);

결과: {"name": "Alice", "age": 30}

4. JSON_ARRAYAGG

여러 행의 값을 하나의 JSON 배열로 집계합니다.

SELECT JSON_ARRAYAGG(name) FROM users;

5. JSON_OBJECTAGG

여러 키-값 쌍을 모아 하나의 JSON 오브젝트를 생성합니다.

SELECT JSON_OBJECTAGG(id, name) FROM users;

6. JSON_INSERT

특정 경로에 데이터가 없을 경우에만 삽입합니다.

SELECT JSON_INSERT('{"name": "Alice"}', '$.age', 30);

7. JSON_REPLACE

특정 경로에 데이터가 존재하면 값을 교체합니다.

SELECT JSON_REPLACE('{"name": "Alice", "age": 25}', '$.age', 30);

8. JSON_REMOVE

특정 키를 제거합니다.

SELECT JSON_REMOVE('{"name": "Alice", "age": 25}', '$.age');

---

복잡화된 JSON 데이터 다루기

1. 중첩(Nested) 키 검색

SELECT JSON_EXTRACT('{
  "user": {
    "profile": {
      "name": "Alice",
      "age": 30
    }
  }
}', '$.user.profile.name') AS username;

결과: "Alice"

2. JSON 배열 요소 접근

SELECT JSON_EXTRACT('{
  "users": [
    {"name": "Alice"},
    {"name": "Bob"},
    {"name": "Charlie"}
  ]
}', '$.users[1].name') AS second_user;

결과: "Bob"

3. 배열에 값 추가 (JSON_ARRAY_APPEND)

SELECT JSON_ARRAY_APPEND('["Alice", "Bob"]', '$', 'Charlie');

결과: ["Alice", "Bob", "Charlie"]

4. JSON 내부 값 수정

SELECT JSON_SET('{
  "user": {
    "name": "Alice",
    "age": 30
  }
}', '$.user.age', 31);

결과: {"user": {"name": "Alice", "age": 31}}

5. 조건 검색

SELECT id, data
FROM users
WHERE JSON_EXTRACT(data, '$.status') = '"active"';

---

MySQL JSON 함수 활용시 주의사항

1. JSON 컬럼은 인덱스를 직접 사용할 수 없다

MySQL의 JSON 데이터 타입은 전통적인 인덱스를 직접 적용할 수 없습니다. 검색 최적화를 위해 가상 컬럼(generated column)을 생성하고 해당 컬럼에 인덱스를 걸어야 합니다.

ALTER TABLE users ADD COLUMN status VARCHAR(255) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(data, '$.status'))) STORED;
CREATE INDEX idx_status ON users(status);

2. JSON 데이터는 문자열 비교가 필요할 수 있다

JSON 데이터는 문자열로 취급될 수 있으므로, 숫자나 불리언 값을 검색할 때 타입 주의가 필요합니다.

3. 데이터 크기와 성능 이슈

JSON 데이터가 지나치게 크거나 깊을 경우 쿼리 성능이 급격히 저하될 수 있습니다. 필요한 경우 데이터 정규화 또는 별도 테이블 분리 전략을 고려해야 합니다.