Ch08. JSON 설계 - JSON 활용

데이터 베이스/설계 2편, 실무에서 반드시 마주치는 9가지 설계 패턴

Ch08. JSON 설계 - JSON 활용

webmaster 2026. 8. 1. 09:22

사례 1: 카테고리별 다른 상품 속성

쇼핑몰에서 가장 흔히 접하는 문제다. 스마트폰, 노트북, 의류, 식품 등 카테고리마다 필요한 속성이 완전히 다르다. 이 문제를 JSON으로 어떻게 해결하는지 살펴보자.

테이블 설계

DROP TABLE IF EXISTS product_catalog;

CREATE TABLE product_catalog (
    product_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(200) NOT NULL,
    category VARCHAR(100) NOT NULL,
    base_price INT NOT NULL,
    attributes JSON,
    created_at DATETIME NOT NULL DEFAULT NOW()
);

공통 속성인 "name", "category", "base_price"는 일반 컬럼으로, 카테고리마다 다른 속성은 "attributes": "JSON" 컬럼으로 관리한다.

다양한 카테고리 상품 등록

-- 스마트폰
INSERT INTO product_catalog (name, category, base_price, attributes) VALUES
('갤럭시 S24 울트라', '스마트폰', 1650000, 
	JSON_OBJECT(
		'screen_size', 6.8,
		'storage_options', JSON_ARRAY(256, 512, 1024),
        'colors', JSON_ARRAY('티타늄 그레이', '티타늄 블랙','티타늄 바이올렛'),
        'ram', 12,
        '5g', true,
        'camera', JSON_OBJECT('main', 200, 'ultra_wide', 12, 'telephoto', 50),
        'battery', 5000,
        'os', 'Android 14'
));

-- 노트북
INSERT INTO product_catalog (name, category, base_price, attributes) VALUES
('맥북 프로 14 M3', '노트북', 2390000, 
	JSON_OBJECT(
		'screen_size', 14.2,
		'storage_options', JSON_ARRAY(512, 1024, 2048),
		'colors', JSON_ARRAY('스페이스 그레이','실버'),
		'ram_options', JSON_ARRAY(18, 36),
		'cpu', 'Apple M5 Pro',
		'gpu', 'Apple M5 Pro 14코어',
		'battery_life', 17,
		'weight', 1.55,
		'ports', JSON_ARRAY('HDMI', 'USB-C', 'MagSafe', 'SD카드')
));

-- 의류
INSERT INTO product_catalog (name, category, base_price, attributes) VALUES
('오버핏 맨투맨', '의류', 45000, 
	JSON_OBJECT(
		'sizes', JSON_ARRAY('S', 'M', 'L', 'XL', 'XXL'),
		'colors', JSON_ARRAY('블랙', '화이트', '그레이','네이비'),
		'material', '면 100%',
		'gender', 'unisex',
		'season', JSON_ARRAY('봄','가을'),
		'care_instructions', JSON_ARRAY('세탁기 사용 가능', '표백제 사용 금지','낮은 온도 다림질')
));

-- 식품
INSERT INTO product_catalog (name, category, base_price, attributes) VALUES
('유기농 아보카도', '식품', 8900,
	JSON_OBJECT(
		'origin', '멕시코',
		'unit', '개',
		'quantity', 3,
		'organic', true,
		'storage', '냉장 보관',
		'shelf_life_days', 7,
		'nutrition', 
			JSON_OBJECT(
				'calories', 160, 'fat', 15,
                'protein', 2,
				'carbs', 9,
				'fiber', 7
			),
		'allergens', JSON_ARRAY()
));

-- 가전제품
INSERT INTO product_catalog (name, category, base_price, attributes) VALUES
('LG 스탠바이미', '가전', 1290000, 
	JSON_OBJECT(
		'screen_size', 27,
		'resolution', '1920x1080',
		'panel_type', 'IPS',
		'touch_screen', true,
		'battery_life', 3,
		'weight', 8.5,
		'speakers', JSON_OBJECT('type', '내장', 'output', '20W'),
		'connectivity', JSON_ARRAY('Wi-Fi', 'Bluetooth', 'HDMI', 'USB'),
		'os', 'webOS'
));

카테고리별 속성 조회

-- 스마트폰 정보 조회
SELECT
	name,
	base_price,
	attributes->>'$.screen_size' AS screen_size,
	attributes->>'$.ram' AS ram,
	attributes->>'$.battery' AS battery,
	attributes->>'$.camera.main' AS main_camera
FROM product_catalog
WHERE category = '스마트폰';

-- 식품 영양정보 조회
SELECT
    name,
    attributes->>'$.origin' AS origin,
    attributes->>'$.nutrition.calories' AS calories,
    attributes->>'$.nutrition.protein' AS protein,
    attributes->>'$.organic' AS organic
FROM product_catalog
WHERE category = '식품';

사례 2: 상품 옵션과 재고 관리

쇼핑몰에서 하나의 상품이 여러 옵션(색상, 사이즈 조합)을 가지는 경우가 많다. 이를 JSON으로 관리하는 방법을 알아보자.

DROP TABLE IF EXISTS product_variant;
CREATE TABLE product_variant (
    product_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(200) NOT NULL,
    base_price INT NOT NULL,
    variants JSON NOT NULL,
    created_at DATETIME NOT NULL DEFAULT NOW()
);

옵션 데이터 등록

INSERT INTO product_variant (name, base_price, variants) VALUES
('나이키 에어맥스', 189000, 
	JSON_ARRAY(
		JSON_OBJECT('sku', 'AM-BLK-250', 'color', '블랙', 'size', 250, 'stock', 15, 'price_adjust', 0),
		JSON_OBJECT('sku', 'AM-BLK-255', 'color','블랙', 'size', 255, 'stock', 23, 'price_adjust', 0),
		JSON_OBJECT('sku', 'AM-BLK-260', 'color', '블랙', 'size', 260, 'stock', 18, 'price_adjust', 0),
		JSON_OBJECT('sku', 'AM-BLK-265', 'color', '블랙', 'size', 265, 'stock', 12, 'price_adjust', 0),
		JSON_OBJECT('sku', 'AM-WHT-250', 'color', '화이트', 'size', 250, 'stock', 8, 'price_adjust', 0),
		JSON_OBJECT('sku', 'AM-WHT-255', 'color', '화이트', 'size', 255, 'stock', 20, 'price_adjust', 0),
		JSON_OBJECT('sku', 'AM-WHT-260', 'color', '화이트', 'size', 260, 'stock', 5, 'price_adjust', 0),
		JSON_OBJECT('sku', 'AM-LTD-260', 'color', '한정판 골드', 'size', 260, 'stock', 3, 'price_adjust', 50000)
));

JSON 배열의 경우에는 다루기가 까다롭다.
이렇게 만들어진 JSON 배열을 마치 테이블처럼 편리하게 다루는 방법을 알아보자.

JSON_TABLE로 옵션 펼치기

MySQL 8.0의 "JSON_TABLE" 함수를 사용하면 JSON 배열을 별도의 테이블처럼 펼칠 수 있다.

JSON_TABLE(
    json_doc,
    path
    COLUMNS (
        column_name data_type PATH json_path [ERROR | EMPTY handling],
        ...
    )
)

"JSON_TABLE"은 JSON 데이터를 SQL 세계로 끌어오는 "가상 테이블 생성 함수"다.
json_doc: JSON 컬럼 또는 JSON 값
path: JSON_TABLE이 반복할 기준 경로, 보통 배열을 가리킨다.
- $[*]: 루트에 있는 배열의 모든 요소
- $.orders[*] : orders 배열의 모든 요소
COLUMNS : JSON → 컬럼 매핑 정의
- 예) "size INT PATH '$.size'" ("$.size"를 INT형 size 컬럼으로 매핑한다.)
오류 처리 옵션 (기본값: "NULL ON EMPTY", "NULL ON ERROR")
- ERROR ON ERROR: 변환 실패 시 오류
- NULL ON ERROR: 실패 시 NULL
- ERROR ON EMPTY: 값 없음 → 오류
- NULL ON EMPTY: 값 없음 → NULL

SELECT
	p.name,
	p.base_price,
	v.sku,
	v.color,
	v.size,
	v.stock,
	(p.base_price + v.price_adjust) AS final_price
FROM product_variant p,
JSON_TABLE(p.variants, '$[*]' COLUMNS (
	sku VARCHAR(50) PATH '$.sku',
	color VARCHAR(50) PATH '$.color',
	size INT PATH '$.size',
	stock INT PATH '$.stock',
	price_adjust INT PATH '$.price_adjust'
)) AS v;

이렇게 하면 p와 v는 조인을 통해서 결과를 생성한다.

특정 조건의 옵션 찾기

-- 재고가 10개 이하인 옵션 찾기
SELECT
    p.name,
    v.sku,
    v.color,
    v.size,
    v.stock
FROM product_variant p,
JSON_TABLE(p.variants, '$[*]' COLUMNS (
    sku VARCHAR(50) PATH '$.sku',
    color VARCHAR(50) PATH '$.color',
    size INT PATH '$.size',
    stock INT PATH '$.stock'
    )) AS v
WHERE v.stock <= 10;

색상별 총 재고 집계

SELECT
    p.name,
    v.color,
    SUM(v.stock) AS total_stock,
    COUNT(*) AS size_count
FROM product_variant p,
JSON_TABLE(p.variants, '$[*]' COLUMNS (
    color VARCHAR(50) PATH '$.color',
    stock INT PATH '$.stock'
)) AS v
GROUP BY p.name, v.color;

사례 3: 사용자 설정 저장

사용자마다 다른 설정을 저장해야 할 때 JSON이 유용하다. 알림 설정, UI 설정, 개인화 설정 등이 대표적이다.

DROP TABLE IF EXISTS user_settings;
CREATE TABLE user_settings (
    user_id BIGINT PRIMARY KEY,
    username VARCHAR(100) NOT NULL,
    settings JSON,
    created_at DATETIME NOT NULL DEFAULT NOW(),
    updated_at DATETIME NOT NULL DEFAULT NOW() ON UPDATE NOW()
);

사용자 설정 등록

INSERT INTO user_settings (
    user_id, 
    username, 
    settings
) 
VALUES 
(
    1, 
    'kim_user', 
    JSON_OBJECT(
        'notifications', JSON_OBJECT(
            'email', true,
            'push', true,
            'sms', false,
            'marketing', false,
            'frequency', 'realtime'
        ),
        'display', JSON_OBJECT(
            'theme', 'dark',
            'language', 'ko',
            'timezone', 'Asia/Seoul',
            'items_per_page', 20
        ),
        'privacy', JSON_OBJECT(
            'profile_visible', true,
            'show_online_status', false,
            'allow_friend_requests', true
        )
    )
),
(
    2, 
    'lee_user', 
    JSON_OBJECT(
        'notifications', JSON_OBJECT(
            'email', true,
            'push', false,
            'sms', true,
            'marketing', true,
            'frequency', 'daily'
        ),
        'display', JSON_OBJECT(
            'theme', 'light',
            'language', 'en',
            'timezone', 'America/New_York',
            'items_per_page', 50
        ),
        'privacy', JSON_OBJECT(
            'profile_visible', false,
            'show_online_status', false,
            'allow_friend_requests', false
        )
    )
);

특정 설정 조회

SELECT
    username,
    settings->>'$.display.theme' AS theme,
    settings->>'$.display.language' AS language,
    settings->>'$.notifications.email' AS email_notification
FROM user_settings;

특정 설정만 업데이트

-- 테마만 변경
UPDATE user_settings
SET settings = JSON_SET(settings, '$.display.theme', 'auto')
WHERE user_id = 1;

-- 결과 확인
SELECT username, settings->>'$.display.theme' AS theme
FROM user_settings WHERE user_id = 1;

사례 4: 로그와 이벤트 데이터

사용자 행동 로그, 시스템 이벤트 등 구조가 다양한 데이터를 저장할 때 JSON이 효과적이다.

테이블 설계

DROP TABLE IF EXISTS event_log;
CREATE TABLE event_log (
	log_id BIGINT PRIMARY KEY AUTO_INCREMENT,
	event_type VARCHAR(100) NOT NULL,
	user_id BIGINT,
	event_data JSON NOT NULL,
	created_at DATETIME NOT NULL DEFAULT NOW(),
	INDEX idx_event_type (event_type),
	INDEX idx_user_id (user_id),
	INDEX idx_created_at (created_at)
);

다양한 이벤트 로그 등록

-- 페이지 조회 이벤트
INSERT INTO event_log (
    event_type, 
    user_id, 
    event_data
) 
VALUES (
    'page_view', 
    1, 
    JSON_OBJECT(
        'page', '/products/phone/galaxy-s24',
        'referrer', 'https://google.com',
        'duration_seconds', 45,
        'device', JSON_OBJECT(
            'type', 'mobile', 
            'os', 'iOS', 
            'browser', 'Safari'
        )
    )
);

-- 검색 이벤트
INSERT INTO event_log (
    event_type, 
    user_id, 
    event_data
) 
VALUES (
    'search', 
    1, 
    JSON_OBJECT(
        'query', '갤럭시 케이스',
        'filters', JSON_OBJECT(
            'category', '액세서리', 
            'price_max', 50000
        ),
        'results_count', 42,
        'clicked_position', 3
    )
);

-- 장바구니 추가 이벤트
INSERT INTO event_log (
    event_type, 
    user_id, 
    event_data
) 
VALUES (
    'add_to_cart', 
    1, 
    JSON_OBJECT(
        'product_id', 101,
        'product_name', '갤럭시 S24',
        'quantity', 1,
        'price', 1200000,
        'cart_total', 1200000
    )
);

-- 구매 완료 이벤트
INSERT INTO event_log (
    event_type, 
    user_id, 
    event_data
) 
VALUES (
    'purchase', 
    1, 
    JSON_OBJECT(
        'order_id', 10001,
        'items_count', 2,
        'total_amount', 1270000,
        'payment_method', 'card',
        'coupon_used', 'WELCOME10'
    )
);

-- 시스템 에러 이벤트
INSERT INTO event_log (
    event_type, 
    user_id, 
    event_data
) 
VALUES (
    'error', 
    NULL, 
    JSON_OBJECT(
        'error_code', 'DB_ERROR',
        'message', 'Failed to connect to database',
        'server', 'db1',
        'stack_trace', '...',
        'level', 'critical'
    )
);

이벤트 타입별 조회

-- 사용자 1의 검색 이벤트 분석
SELECT
    event_data->>'$.query' AS search_query,
    event_data->>'$.results_count' AS results,
    event_data->>'$.clicked_position' AS clicked_at,
    created_at
FROM event_log
WHERE event_type = 'search' AND user_id = 1;

-- 사용자 1의 전체 이벤트 분석
SELECT
    event_type,
    event_data->>'$.page' AS page,
    event_data->>'$.query' AS search_query,
    event_data->>'$.results_count' AS results,
    event_data->>'$.clicked_position' AS clicked_at,
    event_data->>'$.product_name' AS name,
    event_data->>'$.total_amount' AS amount,
    created_at
FROM event_log
WHERE user_id = 1;

-- 시스템 에러 조회
SELECT
    event_data->>'$.error_code' AS error_code,
    event_data->>'$.message' AS message,
    event_data->>'$.level' AS level,
    created_at
FROM event_log
WHERE event_type = 'error';

정리

주요 활용 사례는 다음과 같다.

카테고리별 다른 상품 속성 관리
상품 옵션과 재고 관리 (JSON_TABLE 활용)
다국어 지원
사용자 설정 저장
주문 정보 스냅샷 보존
로그와 이벤트 데이터

핵심 기법은 다음과 같다.

"JSON_TABLE" : JSON 배열을 테이블처럼 펼쳐서 조회
"JSON_OBJECT", "JSON_ARRAY" : 구조화된 JSON 생성
"JSON_SET", "JSON_INSERT" : 부분 업데이트
중첩 경로 접근: "$.parent.child.value"

'데이터 베이스 > 설계 2편, 실무에서 반드시 마주치는 9가지 설계 패턴' 카테고리의 다른 글

Ch08. JSON 설계 - JSON 설계의 장단점과 한계 (0)	2026.08.01
Ch08. JSON 설계 - JSON 인덱스와 성능 최적화 (0)	2026.08.01
Ch08. JSON 설계 - JSON 사용하기(2) (0)	2026.08.01
Ch08. JSON 설계 - JSON 사용하기(1) (0)	2026.07.31
Ch08. JSON 설계 - JSON 문법 (0)	2026.07.29

현재글Ch08. JSON 설계 - JSON 활용

마우스잡기, +, 98\, JdbcTemplate,

Today :
Yesterday :

WebMaster