[MySQL] JSON 컬럼 SELECT 조회 완벽 가이드

최근 MySQL을 사용하는 많은 개발자들이 정형 데이터 외에도 유연하게 구조화된 JSON 데이터를 다루는 상황이 많아졌습니다. MySQL은 5.7부터 JSON 데이터 타입을 정식으로 지원하며, 다양한 JSON 관련 함수도 제공하고 있습니다.

✅ MySQL에서 JSON 타입이란?

MySQL에서 JSON 타입은 유효한 JSON 형식 문자열을 저장하는 컬럼 타입입니다. 일반적인 VARCHAR나 TEXT와는 달리, JSON 구조로 저장되며 내부적으로 BLOB 형태로 최적화되어 저장됩니다.

JSON 컬럼 생성 예시

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    info JSON
);

JSON 데이터 삽입 예시

INSERT INTO users (info)
VALUES ('{"name": "홍길동", "age": 30, "email": "hong@example.com"}');

 

반응형

✅ SELECT 문에서 JSON 컬럼 조회하기

전체 JSON 데이터 조회

SELECT info
FROM users;

이렇게 하면 JSON 문자열 전체가 출력됩니다. 하지만 실무에서는 대부분 JSON 내부의 특정 값을 추출해야 합니다.

✅ JSON 내부 키값 추출 (JSON_EXTRACT, -> 연산자)

JSON_EXTRACT 함수 사용

SELECT JSON_EXTRACT(info, '$.name') AS name
FROM users;

-> 연산자 사용 (축약형)

SELECT info->'$.name' AS name
FROM users;

->> 연산자 사용 (문자열로 추출)

SELECT info->>'$.age' AS age_str
FROM users;

• ->는 JSON 형식 유지
• ->>는 순수 문자열로 반환

✅ JSON 컬럼에서 조건 검색하기

예제: 이름이 ‘홍길동’인 사용자 검색

SELECT *
FROM users
WHERE JSON_EXTRACT(info, '$.name') = '홍길동';

또는

SELECT *
FROM users
WHERE info->>'$.name' = '홍길동';

주의! ->>는 문자열 비교를 할 수 있기 때문에 조건 검색 시 더 자주 사용됩니다.

반응형

✅ JSON 배열 데이터 조회하기

CREATE TABLE posts (
    id INT,
    tags JSON
);

INSERT INTO posts VALUES (1, '["mysql", "database", "json"]');

특정 태그를 포함하는 게시글 찾기

SELECT *
FROM posts
WHERE JSON_CONTAINS(tags, '"json"');

배열 내부 요소 출력

SELECT JSON_EXTRACT(tags, '$[0]') AS first_tag
FROM posts;

 

✅ 실전 예제: 쇼핑몰 사용자 활동 로그

CREATE TABLE user_logs (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    log_data JSON
);

JSON 데이터 예시

{
  "action": "login",
  "ip": "192.168.0.1",
  "device": "mobile"
}

SELECT로 특정 필드 추출

SELECT
    log_data->>'$.action' AS action,
    log_data->>'$.ip' AS ip_address
FROM user_logs;

로그인 로그만 필터링

SELECT *
FROM user_logs
WHERE log_data->>'$.action' = 'login';

 

반응형

✅ 자주 쓰는 MySQL JSON 함수 정리

함수명	설명
JSON_EXTRACT()	JSON에서 경로를 지정해 데이터 추출
JSON_UNQUOTE()	따옴표 제거 후 문자열 반환
JSON_CONTAINS()	특정 값이 포함되어 있는지 확인
JSON_KEYS()	JSON 객체의 키 목록 반환
JSON_ARRAY()	JSON 배열 생성
JSON_OBJECT()	JSON 객체 생성
JSON_SET()	기존 JSON에 새 값 설정
JSON_REMOVE()	JSON에서 특정 키 제거

 

✅ 성능 팁 & 주의사항

• JSON 컬럼에는 일반 인덱스를 적용할 수 없습니다. 대신 가상 컬럼(Generated Column) + 인덱스 조합을 사용하세요.
• JSON은 유연하지만 과도한 중첩 구조는 성능에 불리합니다.
• 정규화된 데이터 구조와 JSON 컬럼을 혼합 설계하는 것이 MySQL에서는 권장되는 방식입니다.

✅ 공식 가이드 문서 링크

• JSON Functions: https://dev.mysql.com/doc/refman/8.0/en/json.html
• JSON Path Syntax: https://dev.mysql.com/doc/refman/8.0/en/json-path-syntax.html