MySQL JSON 컬럼 쿼리 심화

MySQL JSON 컬럼이란?

MySQL 5.7부터 도입된 JSON 데이터 타입은 구조화된 JSON 데이터를 네이티브로 저장하고 쿼리할 수 있다. 8.0에서 대폭 강화되어 JSON 경로 표현식, 인덱싱, 집계 함수까지 지원한다. 스키마 변경 없이 유연한 데이터를 저장해야 하는 경우 — 설정값, 메타데이터, 이벤트 페이로드 등 — JSON 컬럼이 정답이다.

기본 CRUD

-- 테이블 생성
CREATE TABLE products (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  attributes JSON NOT NULL,      -- JSON 컬럼
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- INSERT: JSON 문자열 또는 JSON_OBJECT()
INSERT INTO products (name, attributes) VALUES
  ('MacBook Pro', '{"cpu": "M3 Pro", "ram": 36, "storage": "1TB", "ports": ["USB-C", "HDMI", "MagSafe"]}'),
  ('ThinkPad X1', JSON_OBJECT('cpu', 'i7-1365U', 'ram', 32, 'storage', '512GB', 'ports', JSON_ARRAY('USB-C', 'USB-A', 'HDMI')));

-- UPDATE: JSON_SET (키 추가/수정), JSON_REPLACE (기존 키만 수정), JSON_REMOVE (삭제)
UPDATE products
SET attributes = JSON_SET(attributes, '$.color', 'Space Black', '$.ram', 64)
WHERE name = 'MacBook Pro';

UPDATE products
SET attributes = JSON_REMOVE(attributes, '$.ports[2]')
WHERE name = 'MacBook Pro';

JSON 경로 표현식

표현식	설명	예시
$.key	최상위 키 접근	$.cpu → “M3 Pro”
$.arr[N]	배열 인덱스	$.ports[0] → “USB-C”
$.obj.nested	중첩 객체	$.specs.display.size
$.*	모든 키	와일드카드
$[*]	모든 배열 요소	배열 전체 순회
$**.key	재귀 검색	모든 깊이에서 key 찾기

조회: ->와 ->> 연산자

-- -> : JSON 타입으로 반환 (따옴표 포함)
SELECT attributes->'$.cpu' FROM products;
-- 결과: "M3 Pro" (JSON 문자열)

-- ->> : 텍스트로 반환 (따옴표 없음, WHERE에서 사용)
SELECT attributes->>'$.cpu' FROM products;
-- 결과: M3 Pro (일반 문자열)

-- WHERE 절에서 사용
SELECT name, attributes->>'$.ram' AS ram_gb
FROM products
WHERE attributes->>'$.cpu' LIKE '%M3%'
  AND CAST(attributes->>'$.ram' AS UNSIGNED) >= 32;

-- 배열 요소 검색
SELECT name FROM products
WHERE JSON_CONTAINS(attributes->'$.ports', '"HDMI"');

-- 배열에 여러 값 중 하나라도 포함
SELECT name FROM products
WHERE JSON_OVERLAPS(attributes->'$.ports', '["Thunderbolt", "USB-A"]');

JSON 함수 심화

-- JSON_EXTRACT: 여러 경로 동시 추출
SELECT JSON_EXTRACT(attributes, '$.cpu', '$.ram') FROM products;
-- 결과: ["M3 Pro", 36]

-- JSON_KEYS: 최상위 키 목록
SELECT JSON_KEYS(attributes) FROM products;
-- 결과: ["cpu", "ram", "storage", "ports"]

-- JSON_LENGTH: 배열 길이 또는 객체 키 수
SELECT name, JSON_LENGTH(attributes->'$.ports') AS port_count FROM products;

-- JSON_TYPE: 값의 타입 확인
SELECT JSON_TYPE(attributes->'$.ram') FROM products;
-- 결과: INTEGER

-- JSON_VALID: JSON 유효성 검증
SELECT JSON_VALID('{"valid": true}');   -- 1
SELECT JSON_VALID('invalid json');       -- 0

-- JSON_MERGE_PATCH: 객체 병합 (덮어쓰기)
UPDATE products
SET attributes = JSON_MERGE_PATCH(attributes, '{"warranty": "3years", "ram": 48}')
WHERE id = 1;

-- JSON_MERGE_PRESERVE: 배열은 합치기
SELECT JSON_MERGE_PRESERVE('["a"]', '["b"]');
-- 결과: ["a", "b"]

JSON_TABLE: JSON을 관계형 테이블로 변환

-- JSON 배열을 행으로 풀기 (MySQL 8.0)
SELECT p.name, jt.port
FROM products p,
JSON_TABLE(
  p.attributes->'$.ports',
  '$[*]' COLUMNS (
    port VARCHAR(50) PATH '$'
  )
) AS jt;

-- 결과:
-- MacBook Pro | USB-C
-- MacBook Pro | HDMI
-- MacBook Pro | MagSafe

-- 중첩 JSON 파싱
SELECT o.id, jt.*
FROM orders o,
JSON_TABLE(
  o.items,                    -- JSON 배열 컬럼
  '$[*]' COLUMNS (
    product_name VARCHAR(100) PATH '$.name',
    quantity INT PATH '$.qty',
    price DECIMAL(10,2) PATH '$.price',
    total DECIMAL(10,2) PATH '$.total' DEFAULT '0' ON EMPTY
  )
) AS jt
WHERE jt.quantity > 1;

JSON_TABLE은 JSON 데이터를 일반 테이블처럼 JOIN, GROUP BY, 집계할 수 있게 해주는 강력한 함수다.

인덱싱: Generated Column + B-Tree

JSON 컬럼 자체는 인덱스를 걸 수 없다. Generated Column(가상 컬럼)을 만들어 인덱싱한다.

-- 가상 컬럼 + 인덱스
ALTER TABLE products
ADD COLUMN cpu VARCHAR(100) GENERATED ALWAYS AS (attributes->>'$.cpu') STORED,
ADD INDEX idx_cpu (cpu);

-- 이제 일반 컬럼처럼 인덱스 사용
SELECT * FROM products WHERE cpu = 'M3 Pro';
-- EXPLAIN → type: ref, key: idx_cpu ✅

-- 숫자 필드 인덱싱
ALTER TABLE products
ADD COLUMN ram_gb INT GENERATED ALWAYS AS (CAST(attributes->>'$.ram' AS UNSIGNED)) STORED,
ADD INDEX idx_ram (ram_gb);

-- MySQL 8.0.17+: Multi-Valued Index (배열 검색용)
ALTER TABLE products
ADD INDEX idx_ports ((CAST(attributes->'$.ports' AS CHAR(50) ARRAY)));

-- Multi-Valued Index로 배열 검색
SELECT * FROM products
WHERE JSON_CONTAINS(attributes->'$.ports', '"USB-C"');
-- EXPLAIN → Multi-Valued Index 사용 ✅

MySQL 인덱스 최적화에서 B-Tree, Covering Index 등 기본 인덱싱 전략을 확인할 수 있다.

ORM 연동: TypeORM과 JPA

// TypeORM — simple-json 또는 json 타입
@Entity()
export class Product {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  name: string;

  @Column({ type: 'json' })
  attributes: {
    cpu: string;
    ram: number;
    ports: string[];
  };
}

// QueryBuilder에서 JSON 경로 사용
const products = await productRepository
  .createQueryBuilder('p')
  .where("p.attributes->>'$.ram' >= :ram", { ram: 32 })
  .andWhere("JSON_CONTAINS(p.attributes->'$.ports', :port)", { port: '"USB-C"' })
  .getMany();

// JPA — AttributeConverter
@Entity
public class Product {
    @Convert(converter = JsonConverter.class)
    @Column(columnDefinition = "json")
    private Map<String, Object> attributes;
}

// Native Query에서 JSON 함수 사용
@Query(value = "SELECT * FROM products WHERE attributes->>'$.cpu' = :cpu", nativeQuery = true)
List<Product> findByCpu(@Param("cpu") String cpu);

JSON vs 정규화: 언제 JSON을 쓸까?

JSON 컬럼 적합	정규화 테이블 적합
스키마가 유동적 (상품 속성, 설정)	스키마가 고정적 (주문, 사용자)
조회보다 저장이 중요	빈번한 WHERE/JOIN 대상
API 페이로드 원본 저장	집계/리포트 대상 데이터
EAV(Entity-Attribute-Value) 대체	외래 키 관계가 필요한 경우

주의사항

• JSON 컬럼 크기 — MySQL JSON은 최대 1GB지만, 커넥션 풀과 네트워크 대역폭을 고려하면 수 KB 이내를 권장한다.
• 부분 업데이트 비용 — JSON_SET은 내부적으로 전체 JSON을 재작성한다. 빈번한 부분 수정이 필요하면 정규화를 고려하라. MySQL 8.0의 Partial Update 최적화가 일부 케이스에서 동작하지만 보장은 아니다.
• 인덱스 필수 — Generated Column이나 Multi-Valued Index 없이 ->> 연산자로 WHERE를 걸면 풀 테이블 스캔이다. EXPLAIN으로 반드시 확인하라.
• 타입 안전성 — JSON은 스키마 강제가 없다. 애플리케이션 레벨에서 검증(Zod, class-validator)을 반드시 수행해야 한다.

정리

MySQL JSON 컬럼은 유연한 스키마 데이터를 RDB 안에서 관리하는 실용적인 솔루션이다. ->> 연산자로 경로 기반 조회, JSON_TABLE로 관계형 변환, Generated Column으로 인덱싱하면 NoSQL 없이도 반정형 데이터를 효과적으로 다룰 수 있다. 핵심은 “조회 패턴에 따라 인덱스를 걸고, 정규화와 JSON의 경계를 명확히 하는 것”이다.