MySQL에서 JSON 데이터 처리하기 (저장·조회·수정·인덱싱)

스키마 변화가 잦거나 다양한 속성을 담아야 할 때 JSON 타입은 유연한 해법입니다.

 “언제, 왜, 어떻게”를 중심으로 저장·조회·수정·인덱싱·집계 방법을 정리 했습니다.

 

1) JSON 컬럼 정의: 가장 간단한 시작

CREATE TABLE products (
  id         BIGINT PRIMARY KEY AUTO_INCREMENT,
  name       VARCHAR(120) NOT NULL,
  attrs      JSON NOT NULL,             -- 임의 속성 보관
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

포인트: JSON 타입은 올바른 JSON만 저장됩니다(유효성 자동 검사). 반정형 데이터를 담기 좋지만, 조인/검색 빈도가 높은 키는 정규 컬럼으로 두는 것이 성능에 유리합니다.

 

 

2) 읽기/조회: 경로(path) 문법과 단축 연산자

• $는 루트, 점(.)으로 객체 키 접근, 대괄호로 배열 인덱스 접근
• -> JSON 값 반환, ->> 텍스트로 변환해 반환

-- 예시 데이터
INSERT INTO products (name, attrs) VALUES
('AirPods', JSON_OBJECT('brand','Apple','color','white','tags',JSON_ARRAY('audio','bt'),'weight',5.4)),
('Galaxy Buds', JSON_OBJECT('brand','Samsung','color','black','tags',JSON_ARRAY('audio'),'waterproof',true));

-- 키/배열 접근
SELECT
  id, name,
  JSON_EXTRACT(attrs, '$.brand')         AS brand_json,
  attrs->>'$.color'                      AS color_text,
  JSON_EXTRACT(attrs, '$.tags[0]')       AS first_tag
FROM products;

-- 조건 검색
SELECT id, name
FROM products
WHERE JSON_EXTRACT(attrs, '$.brand') = JSON_QUOTE('Apple');  -- JSON 비교

-- 문자열 비교가 필요하면 →> 사용
SELECT id, name
FROM products
WHERE attrs->>'$.brand' = 'Apple';

Tip: 단일 값 비교는 ->>(텍스트)로 캐스팅해 단순 비교를 쓰면 가독성이 좋아집니다.

 

 

3) 수정·추가·삭제: 부분 업데이트로 쓰기 비용 최소화

-- 값 추가/갱신 (있으면 바꾸고 없으면 추가)
UPDATE products
SET attrs = JSON_SET(attrs, '$.price', 199.0, '$.specs.btVersion', '5.3')
WHERE name = 'AirPods';

-- 존재할 때만 교체
UPDATE products
SET attrs = JSON_REPLACE(attrs, '$.color', 'pink')
WHERE id = 1;

-- 키 제거
UPDATE products
SET attrs = JSON_REMOVE(attrs, '$.weight');

-- 여러 문서를 병합(패치 의미, null은 제거 아님)
UPDATE products
SET attrs = JSON_MERGE_PATCH(attrs, JSON_OBJECT('discount', 10));

비교: JSON_SET(Upsert), JSON_REPLACE(존재 시만), JSON_INSERT(없을 때만 추가), JSON_REMOVE(삭제).

 

 

4) 검색 성능 높이기: 생성 열 + 인덱스(또는 함수 인덱스)

JSON은 직접 인덱스 대상이 아닙니다. 생성 열(Generated Column)로 값을 뽑아 인덱스하세요. MySQL 8.0.13+는 함수 기반 인덱스도 지원합니다.

-- (A) 생성 열 + 보조 인덱스
ALTER TABLE products
  ADD brand VARCHAR(40) GENERATED ALWAYS AS (attrs->>'$.brand') STORED,
  ADD INDEX idx_brand (brand);

-- (B) 함수 기반 인덱스(8.0.13+)
CREATE INDEX idx_attrs_brand ON products ((JSON_UNQUOTE(JSON_EXTRACT(attrs, '$.brand'))));

-- 인덱스 활용 조회
EXPLAIN SELECT id, name FROM products WHERE attrs->>'$.brand' = 'Apple';

권장: 자주 필터/정렬하는 키는 생성 열 + STORED + 인덱스 조합이 가장 관리하기 쉽습니다.

 

 

5) JSON 집계·변환: API 응답 만들기에도 딱

-- 배열로 모으기
SELECT JSON_ARRAYAGG(name) AS names FROM products;

-- 객체로 모으기 (key=value 형태)
SELECT JSON_OBJECTAGG(id, name) AS id_to_name FROM products;

-- 특정 필드만 추출한 결과를 JSON으로
SELECT
  JSON_OBJECT('id', id, 'name', name, 'brand', attrs->>'$.brand') AS doc
FROM products;

-- 보기 좋게 출력(로그/디버그)
SELECT JSON_PRETTY(attrs) FROM products WHERE id = 1;

활용: REST 응답을 DB에서 바로 JSON으로 만들 수 있어 애플리케이션 후처리가 단순해집니다.

 

 

6) 유효성·제약: 최소한의 스키마 가드

-- 올바른 JSON인지 검사 (INSERT/UPDATE 전에 체크 용)
SELECT JSON_VALID(attrs) FROM products WHERE id = 1;

-- 필수 키 존재/타입 제약을 CHECK로 보강(8.0+)
ALTER TABLE products
ADD CONSTRAINT chk_price CHECK (
  JSON_TYPE(JSON_EXTRACT(attrs,'$.price')) IN ('INTEGER','DOUBLE')  -- 숫자
);

팁: 중요한 키(예: brand, price)는 생성 열로 끌어내 정규 컬럼처럼 NOT NULL/타입 제약으로 관리하면 더 안전합니다.

 

 

7) 실전 팁 & 선택 기준

• JSON이 답일 때: 속성 종류가 많고 변동이 잦아 테이블 스키마 변경이 부담스러울 때.
• 정규화가 답일 때: 조인/검색이 잦고 고정 스키마로도 충분할 때(인덱스·참조무결성 이점).
• 성능: 대량 쓰기 전에 인덱스(특히 생성 열 인덱스)는 잠시 끄고 적재 후 생성하면 빠릅니다.
• 경로 안전: 없는 경로 접근은 NULL을 반환. JSON_EXTRACT + JSON_TYPE으로 방어 코드를 두세요.
• 부분 업데이트: 전체 문서 재작성 대신 JSON_SET/REMOVE를 써서 I/O를 줄입니다.

 

8) 자주 쓰는 스니펫 모음

-- 1) 배열에 값 추가(중복 허용)
UPDATE products
SET attrs = JSON_ARRAY_APPEND(attrs, '$.tags', 'wireless')
WHERE id = 1;

-- 2) 배열에 값이 포함되어 있는지
SELECT id, name
FROM products
WHERE JSON_CONTAINS(attrs, JSON_ARRAY('audio'), '$.tags');  -- 부분 포함

-- 3) 다중 필터(브랜드=Apple, 방수 true)
SELECT id, name
FROM products
WHERE attrs->>'$.brand' = 'Apple'
  AND JSON_EXTRACT(attrs, '$.waterproof') = true;

-- 4) 키 존재 여부
SELECT id FROM products
WHERE JSON_CONTAINS_PATH(attrs, 'one', '$.specs.btVersion');

-- 5) 부분 문서만 업데이트(트랜잭션 내)
START TRANSACTION;
UPDATE products
SET attrs = JSON_SET(attrs, '$.inventory', JSON_OBJECT('qty', 50, 'loc', 'ICN'));
COMMIT;

 

 

 

 

MySQL의 JSON은 “스키마 유연성 + SQL 친화성”을 동시에 제공합니다. 핵심 키는 생성 열로 인덱싱하고, 데이터 조작은 JSON_SET/REMOVE로 부분 갱신, 집계는 JSON_ARRAYAGG/OBJECTAGG로 마무리하세요. 이렇게만 해도 대부분의 실무 요구를 가볍게 커버할 수 있습니다.