SQL 25강: JSON 데이터 쿼리
관계형 데이터베이스는 고정된 스키마로 데이터를 관리합니다. 하지만 상품 사양처럼 카테고리마다 속성이 다른 데이터는 어떻게 저장할까요? 노트북에는 screensize, cpu, batteryhours가 필요하고, 그래픽카드에는 vram, clockm…
관계형 데이터베이스는 고정된 스키마로 데이터를 관리합니다. 하지만 상품 사양처럼 카테고리마다 속성이 다른 데이터는 어떻게 저장할까요? 노트북에는 screen_size, cpu, battery_hours가 필요하고, 그래픽카드에는 vram, clock_mhz, tdp_watts가 필요합니다. 카테고리마다 칼럼을 따로 만들면 테이블이 수십 개의 NULL 칼럼으로 채워집니다.
JSON 칼럼은 이 문제를 해결합니다. 스키마를 변경하지 않고도 유연한 속성을 저장할 수 있습니다.
실무에서 JSON 칼럼을 사용하는 대표적인 시나리오:
- 상품 스펙: 카테고리마다 다른 속성 (CPU, RAM, 화면 크기, VRAM 등)
- 설정/환경: 사용자별 UI 설정, 알림 설정 등 유동적인 데이터
- 외부 API 응답: 구조가 자주 바뀌는 외부 데이터를 원본 그대로 저장
- 이벤트 로그: 이벤트마다 다른 필드를 가진 로그 데이터
TechShop의 products.specs 칼럼에는 각 상품의 기술 사양이 JSON 형식으로 저장되어 있습니다. 이 강의에서는 JSON 값을 추출하고, 필터링하고, 수정하는 방법을 배웁니다.
flowchart LR
P["products 테이블"] --> F["고정 칼럼<br>id, name, price"]
P --> J["JSON 칼럼<br>specs"]
J --> L["노트북<br>cpu, ram, screen_size"]
J --> G["GPU<br>vram, clock_mhz, tdp_watts"]
J --> M["모니터<br>resolution, refresh_rate, panel"]
products.specs칼럼은 TEXT 타입이지만 JSON 문자열을 저장합니다. SQL 함수로 JSON 내부 값을 추출하고 필터링할 수 있습니다.
이미 알고 계신다면
json_extract, json_set, JSON 집계에 익숙하다면 26강: 저장 프로시저으로 건너뛰세요.
products.specs 칼럼
이 데이터베이스의 products 테이블에는 specs 칼럼이 있습니다. 카테고리별로 저장되는 JSON 구조가 다릅니다:
-- 노트북 상품의 specs 확인
SELECT name, specs
FROM products
WHERE specs IS NOT NULL
LIMIT 3;
노트북 예시:
{"screen_size": "15.6 inch", "cpu": "Intel Core i7-13700H", "ram": "16GB", "storage": "512GB SSD", "battery_hours": 10}
GPU 예시:
{"vram": "16GB", "clock_mhz": 2100, "tdp_watts": 300}
모니터 예시:
{"screen_size": "27 inch", "resolution": "QHD", "refresh_rate": 144, "panel": "IPS"}
JSON 값 추출
JSON 내부의 특정 키 값을 추출하는 문법은 데이터베이스마다 다릅니다.
-- json_extract 함수 사용
SELECT
name,
json_extract(specs, '$.cpu') AS cpu,
json_extract(specs, '$.ram') AS ram,
json_extract(specs, '$.storage') AS storage
FROM products
WHERE specs IS NOT NULL
AND json_extract(specs, '$.cpu') IS NOT NULL
LIMIT 5;
-- ->> 연산자 (SQLite 3.38+, 텍스트로 반환)
SELECT
name,
specs->>'$.cpu' AS cpu,
specs->>'$.ram' AS ram
FROM products
WHERE specs IS NOT NULL
AND specs->>'$.cpu' IS NOT NULL
LIMIT 5;
-- JSON_EXTRACT 함수 사용
SELECT
name,
JSON_EXTRACT(specs, '$.cpu') AS cpu,
JSON_EXTRACT(specs, '$.ram') AS ram,
JSON_EXTRACT(specs, '$.storage') AS storage
FROM products
WHERE specs IS NOT NULL
AND JSON_EXTRACT(specs, '$.cpu') IS NOT NULL
LIMIT 5;
-- ->> 연산자 (따옴표 없는 텍스트로 반환)
SELECT
name,
specs->>'$.cpu' AS cpu,
specs->>'$.ram' AS ram
FROM products
WHERE specs IS NOT NULL
AND specs->>'$.cpu' IS NOT NULL
LIMIT 5;
-- ->> 연산자 (텍스트로 반환)
SELECT
name,
specs->>'cpu' AS cpu,
specs->>'ram' AS ram,
specs->>'storage' AS storage
FROM products
WHERE specs IS NOT NULL
AND specs->>'cpu' IS NOT NULL
LIMIT 5;
-- jsonb_extract_path_text 함수
SELECT
name,
jsonb_extract_path_text(specs, 'cpu') AS cpu,
jsonb_extract_path_text(specs, 'ram') AS ram
FROM products
WHERE specs IS NOT NULL
AND jsonb_extract_path_text(specs, 'cpu') IS NOT NULL
LIMIT 5;
핵심 차이점:
| 기능 | SQLite | MySQL | PostgreSQL |
|---|---|---|---|
| 경로 문법 | '$.key' |
'$.key' |
'key' |
| 함수 | json_extract() |
JSON_EXTRACT() |
jsonb_extract_path_text() |
| 텍스트 추출 연산자 | ->>'$.key' |
->>'$.key' |
->>'key' |
| JSON 추출 연산자 | ->'$.key' |
->'$.key' |
->'key' |
->연산자는 JSON 타입을 반환하고,->>연산자는 텍스트 타입을 반환합니다. WHERE 절에서 비교할 때는 보통->>(텍스트)를 사용합니다.
JSON WHERE 조건
JSON 값으로 행을 필터링할 수 있습니다.
-- RAM이 32GB인 상품 찾기
SELECT name, price, specs->>'$.ram' AS ram
FROM products
WHERE specs->>'$.ram' = '32GB';
-- 배터리 10시간 이상인 노트북
SELECT name, price, json_extract(specs, '$.battery_hours') AS battery
FROM products
WHERE json_extract(specs, '$.battery_hours') >= 10
ORDER BY json_extract(specs, '$.battery_hours') DESC;
-- RAM이 32GB인 상품 찾기
SELECT name, price, specs->>'$.ram' AS ram
FROM products
WHERE specs->>'$.ram' = '32GB';
-- 배터리 10시간 이상인 노트북
SELECT name, price, JSON_EXTRACT(specs, '$.battery_hours') AS battery
FROM products
WHERE JSON_EXTRACT(specs, '$.battery_hours') >= 10
ORDER BY JSON_EXTRACT(specs, '$.battery_hours') DESC;
-- RAM이 32GB인 상품 찾기
SELECT name, price, specs->>'ram' AS ram
FROM products
WHERE specs->>'ram' = '32GB';
-- 배터리 10시간 이상인 노트북
SELECT name, price, (specs->>'battery_hours')::int AS battery
FROM products
WHERE (specs->>'battery_hours')::int >= 10
ORDER BY (specs->>'battery_hours')::int DESC;
PostgreSQL 타입 캐스팅
PostgreSQL에서 ->> 연산자는 항상 텍스트를 반환합니다. 숫자 비교가 필요하면 ::int나 ::numeric으로 명시적 캐스팅이 필요합니다. SQLite와 MySQL은 자동 타입 변환을 합니다.
JSON 집계
JSON 값을 GROUP BY나 집계 함수에 사용할 수 있습니다.
-- CPU별 상품 수와 평균 가격
SELECT
specs->>'$.cpu' AS cpu,
COUNT(*) AS product_count,
ROUND(AVG(price)) AS avg_price
FROM products
WHERE specs->>'$.cpu' IS NOT NULL
GROUP BY specs->>'$.cpu'
ORDER BY product_count DESC;
-- CPU별 상품 수와 평균 가격
SELECT
specs->>'$.cpu' AS cpu,
COUNT(*) AS product_count,
ROUND(AVG(price)) AS avg_price
FROM products
WHERE specs->>'$.cpu' IS NOT NULL
GROUP BY specs->>'$.cpu'
ORDER BY product_count DESC;
-- CPU별 상품 수와 평균 가격
SELECT
specs->>'cpu' AS cpu,
COUNT(*) AS product_count,
ROUND(AVG(price)) AS avg_price
FROM products
WHERE specs->>'cpu' IS NOT NULL
GROUP BY specs->>'cpu'
ORDER BY product_count DESC;
-- 해상도별 모니터 평균 가격
SELECT
specs->>'$.resolution' AS resolution,
COUNT(*) AS cnt,
ROUND(AVG(price)) AS avg_price,
MIN(price) AS min_price,
MAX(price) AS max_price
FROM products
WHERE specs->>'$.resolution' IS NOT NULL
GROUP BY specs->>'$.resolution'
ORDER BY avg_price DESC;
-- 해상도별 모니터 평균 가격
SELECT
specs->>'$.resolution' AS resolution,
COUNT(*) AS cnt,
ROUND(AVG(price)) AS avg_price,
MIN(price) AS min_price,
MAX(price) AS max_price
FROM products
WHERE specs->>'$.resolution' IS NOT NULL
GROUP BY specs->>'$.resolution'
ORDER BY avg_price DESC;
-- 해상도별 모니터 평균 가격
SELECT
specs->>'resolution' AS resolution,
COUNT(*) AS cnt,
ROUND(AVG(price)) AS avg_price,
MIN(price) AS min_price,
MAX(price) AS max_price
FROM products
WHERE specs->>'resolution' IS NOT NULL
GROUP BY specs->>'resolution'
ORDER BY avg_price DESC;
JSON 키 목록 조회
JSON 객체에 어떤 키가 있는지 확인하는 방법입니다.
-- json_each로 JSON의 모든 키 나열
SELECT DISTINCT j.key
FROM products, json_each(products.specs) AS j
WHERE products.specs IS NOT NULL
ORDER BY j.key;
-- JSON_KEYS로 키 배열 반환
SELECT DISTINCT JSON_KEYS(specs) AS spec_keys
FROM products
WHERE specs IS NOT NULL
LIMIT 10;
-- jsonb_object_keys로 키 나열
SELECT DISTINCT k
FROM products, jsonb_object_keys(specs) AS k
WHERE specs IS NOT NULL
ORDER BY k;
JSON 값 수정
기존 JSON 데이터의 특정 키를 변경하거나 새 키를 추가할 수 있습니다.
-- json_set: 값 수정 (키가 없으면 추가)
UPDATE products
SET specs = json_set(specs, '$.ram', '32GB')
WHERE id = 1;
-- json_insert: 키가 없을 때만 추가 (있으면 무시)
UPDATE products
SET specs = json_insert(specs, '$.color', 'Silver')
WHERE id = 1;
-- json_remove: 키 삭제
UPDATE products
SET specs = json_remove(specs, '$.color')
WHERE id = 1;
-- JSON_SET: 값 수정 (키가 없으면 추가)
UPDATE products
SET specs = JSON_SET(specs, '$.ram', '32GB')
WHERE id = 1;
-- JSON_INSERT: 키가 없을 때만 추가 (있으면 무시)
UPDATE products
SET specs = JSON_INSERT(specs, '$.color', 'Silver')
WHERE id = 1;
-- JSON_REMOVE: 키 삭제
UPDATE products
SET specs = JSON_REMOVE(specs, '$.color')
WHERE id = 1;
-- jsonb_set: 값 수정 (세 번째 인자가 true면 키 없을 때 추가)
UPDATE products
SET specs = jsonb_set(specs, '{ram}', '"32GB"')
WHERE id = 1;
-- || 연산자: 키 추가/병합
UPDATE products
SET specs = specs || '{"color": "Silver"}'::jsonb
WHERE id = 1;
-- - 연산자: 키 삭제
UPDATE products
SET specs = specs - 'color'
WHERE id = 1;
JSON 수정 함수 비교:
| 동작 | SQLite | MySQL | PostgreSQL |
|---|---|---|---|
| 수정/추가 | json_set() |
JSON_SET() |
jsonb_set() |
| 추가만 | json_insert() |
JSON_INSERT() |
|| 연산자 |
| 삭제 | json_remove() |
JSON_REMOVE() |
- 연산자 |
JSON vs 정규화
JSON 칼럼은 강력하지만 만능은 아닙니다.
| 기준 | JSON 칼럼 | 별도 테이블 (정규화) |
|---|---|---|
| 유연성 | 스키마 변경 없이 속성 추가/삭제 | ALTER TABLE 필요 |
| 쿼리 성능 | 인덱스 지원 제한적 | 일반 인덱스, 빠른 조회 |
| 데이터 무결성 | CHECK 제약조건 어려움 | FK, NOT NULL, UNIQUE 가능 |
| 적합한 경우 | 카테고리별 다른 속성, 설정값, 메타데이터 | 자주 검색/조인하는 핵심 데이터 |
| 부적합한 경우 | 매번 JOIN/GROUP BY에 사용하는 칼럼 | 속성이 가변적이고 자주 변하는 경우 |
경험 규칙: WHERE 절이나 JOIN에 자주 사용하는 값이면 정규 칼럼으로 분리하세요. 표시만 하거나 가끔 필터링하는 부가 속성이면 JSON이 적합합니다.
정리
| 개념 | 설명 | 예시 |
|---|
레슨 복습 문제
이 레슨에서 배운 개념을 바로 확인하는 간단한 문제입니다. 여러 개념을 종합하는 실전 연습은 연습 문제 섹션을 참고하세요.
문제 1
RAM이 '16GB'인 상품의 이름, 가격, RAM 값을 조회하세요. 가격 내림차순으로 정렬하세요.
정답
SELECT name, price, specs->>'$.ram' AS ram
FROM products
WHERE specs->>'$.ram' = '16GB'
ORDER BY price DESC;
실행 결과 (총 15행 중 상위 7행)
| name | price | ram |
|---|---|---|
| ASUS ROG Strix G16CH 화이트 | 3,671,500.00 | 16GB |
| ASUS ROG Zephyrus G16 | 3,429,900.00 | 16GB |
| HP EliteBook 840 G10 블랙 [특별 한정판 에디션] ... | 2,080,300.00 | 16GB |
| ASUS ExpertBook B5 화이트 | 2,068,800.00 | 16GB |
| 레노버 ThinkPad X1 2in1 실버 | 1,866,100.00 | 16GB |
| Razer Blade 18 | 1,806,800.00 | 16GB |
| 주연 리오나인 R7 시스템 | 1,800,000.00 | 16GB |
문제 2
products 테이블에서 specs 칼럼이 NULL이 아닌 상품의 이름과 CPU 값을 추출하세요. CPU 값이 있는 상품만 표시하고, 결과를 5건으로 제한하세요.
정답
SELECT name, specs->>'$.cpu' AS cpu
FROM products
WHERE specs IS NOT NULL
AND specs->>'$.cpu' IS NOT NULL
LIMIT 5;
실행 결과 (5행)
| name | cpu |
|---|---|
| Razer Blade 18 블랙 | Apple M3 |
| LG 일체형PC 27V70Q 실버 | Intel Core i5-13600K |
| Razer Blade 18 화이트 | Intel Core i9-13900H |
| 한성 보스몬스터 DX9900 실버 | AMD Ryzen 5 7600X |
| ASUS ROG Strix G16CH 화이트 | AMD Ryzen 5 7600X |
문제 3
specs 칼럼에 사용된 모든 고유 키(key) 목록을 알파벳 순서로 조회하세요.
정답
SELECT DISTINCT j.key
FROM products, json_each(products.specs) AS j
WHERE products.specs IS NOT NULL
ORDER BY j.key;
실행 결과 (총 23행 중 상위 7행)
| key |
|---|
| base_clock_ghz |
| battery_hours |
| boost_clock_ghz |
| capacity_gb |
| clock_mhz |
| cores |
| cpu |
문제 4
specs에 cpu 키가 있는 상품 중, 가격이 가장 비싼 상품 3개의 이름, CPU, 가격을 조회하세요.
정답
SELECT name, specs->>'$.cpu' AS cpu, price
FROM products
WHERE specs->>'$.cpu' IS NOT NULL
ORDER BY price DESC
LIMIT 3;
실행 결과 (3행)
| name | cpu | price |
|---|---|---|
| MacBook Air 15 M3 실버 | Intel Core i9-13900H | 5,481,100.00 |
| Razer Blade 18 블랙 | Intel Core i7-13700H | 4,353,100.00 |
| Razer Blade 16 실버 | AMD Ryzen 9 7945HX | 3,702,900.00 |
문제 5
배터리 수명이 12시간 이상인 노트북의 이름, 가격, 배터리 수명을 조회하세요. 배터리 수명 내림차순으로 정렬하세요.
정답
SELECT
name,
price,
json_extract(specs, '$.battery_hours') AS battery_hours
FROM products
WHERE json_extract(specs, '$.battery_hours') >= 12
ORDER BY json_extract(specs, '$.battery_hours') DESC;
실행 결과 (총 12행 중 상위 7행)
| name | price | battery_hours |
|---|---|---|
| LG 그램 17 실버 | 1,697,400.00 | 15 |
| Razer Blade 18 | 1,806,800.00 | 15 |
| 레노버 ThinkPad X1 Carbon | 1,389,800.00 | 15 |
| MSI Vector 16 HX 실버 | 1,345,900.00 | 15 |
| 레노버 IdeaPad Flex 5 화이트 | 1,657,300.00 | 14 |
| ASUS ROG Strix Scar 16 | 2,452,500.00 | 14 |
| Razer Blade 18 블랙 | 4,353,100.00 | 14 |
문제 6
VRAM이 '16GB' 이상인 GPU 상품의 이름, 가격, VRAM, TDP(전력 소모)를 조회하세요. TDP 오름차순으로 정렬하세요.
정답
SELECT
name,
price,
specs->>'$.vram' AS vram,
json_extract(specs, '$.tdp_watts') AS tdp_watts
FROM products
WHERE specs->>'$.vram' IN ('16GB', '24GB')
ORDER BY json_extract(specs, '$.tdp_watts');
실행 결과 (총 8행 중 상위 7행)
| name | price | vram | tdp_watts |
|---|---|---|---|
| SAPPHIRE PULSE RX 7800 XT 블랙 | 862,500.00 | 16GB | 205 |
| 기가바이트 RTX 4090 AERO OC 화이트 | 1,280,900.00 | 16GB | 301 |
| ASUS Dual RX 9070 실버 | 1,344,800.00 | 24GB | 312 |
| ASUS TUF Gaming RTX 5080 화이트 | 4,526,600.00 | 24GB | 339 |
| SAPPHIRE NITRO+ RX 7900 XTX 블랙 | 867,300.00 | 24GB | 340 |
| MSI Radeon RX 9070 VENTUS 3X 화이트 | 383,100.00 | 16GB | 411 |
| SAPPHIRE PULSE RX 7800 XT 실버 | 1,146,300.00 | 24GB | 427 |
문제 7
상품 ID 1의 specs에 "color" 키를 "Space Gray" 값으로 추가하는 UPDATE 문을 작성하세요. 이후 추가된 값을 조회하여 확인하세요.
정답
-- 키 추가
UPDATE products
SET specs = json_set(specs, '$.color', 'Space Gray')
WHERE id = 1;
-- 확인
SELECT name, specs->>'$.color' AS color
FROM products
WHERE id = 1;
문제 8
연습 8에서 추가한 "color" 키를 상품 ID 1의 specs에서 삭제하는 UPDATE 문을 작성하세요. 이후 삭제됐는지 확인하세요.
정답
-- 키 삭제
UPDATE products
SET specs = json_remove(specs, '$.color')
WHERE id = 1;
-- 확인 (NULL이어야 함)
SELECT name, specs->>'$.color' AS color
FROM products
WHERE id = 1;
문제 9
specs에 screen_size 키가 있는 상품을 화면 크기별로 그룹화하고, 각 그룹의 상품 수와 평균 가격을 조회하세요. 상품 수 내림차순으로 정렬하세요.
정답
SELECT
specs->>'$.screen_size' AS screen_size,
COUNT(*) AS product_count,
ROUND(AVG(price)) AS avg_price
FROM products
WHERE specs->>'$.screen_size' IS NOT NULL
GROUP BY specs->>'$.screen_size'
ORDER BY product_count DESC;
실행 결과 (6행)
| screen_size | product_count | avg_price |
|---|---|---|
| 14 inch | 13 | 2,112,669.00 |
| 27 inch | 12 | 1,085,900.00 |
| 15.6 inch | 10 | 1,765,740.00 |
| 32 inch | 6 | 970,783.00 |
| 16 inch | 6 | 2,522,717.00 |
| 24 inch | 4 | 1,194,175.00 |
문제 10
모니터 패널 타입(panel)별로 상품 수, 평균 주사율(refresh_rate), 최대 주사율을 집계하세요.
정답
SELECT
specs->>'$.panel' AS panel,
COUNT(*) AS product_count,
ROUND(AVG(json_extract(specs, '$.refresh_rate'))) AS avg_refresh_rate,
MAX(json_extract(specs, '$.refresh_rate')) AS max_refresh_rate
FROM products
WHERE specs->>'$.panel' IS NOT NULL
GROUP BY specs->>'$.panel'
ORDER BY avg_refresh_rate DESC;
실행 결과 (3행)
| panel | product_count | avg_refresh_rate | max_refresh_rate |
|---|---|---|---|
| VA | 6 | 135.00 | 240 |
| OLED | 8 | 135.00 | 240 |
| IPS | 8 | 114.00 | 165 |