Ch08. JSON 설계 - JSON 사용하기(1)

이제 MySQL에서 JSON을 실제로 사용하는 방법을 알아보자. MySQL 5.7부터 JSON 타입을 지원하며, 8.0에서 많은 기능이 추가되었다.

 

JSON 컬럼이 있는 테이블 생성

-- 기존 테이블 삭제
DROP TABLE IF EXISTS product_json;

-- JSON 컬럼이 있는 상품 테이블 생성
CREATE TABLE product_json (
	product_id BIGINT PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(200) NOT NULL,
	category VARCHAR(100) NOT NULL,
	attributes JSON,
	created_at DATETIME NOT NULL DEFAULT NOW()
);

• attributes 컬럼의 타입이 JSON인 것을 확인할 수 있다.
• MySQL은 이 컬럼에 유효한 JSON만 저장되도록 자동으로 검증한다.

JSON 데이터 삽입

문자열로 직접 삽입

INSERT INTO product_json (name, category, attributes) VALUES
('갤럭시 S24','스마트폰','{"screen_size": 6.2, "storage": 256, "color": "팬텀 블랙", "ram": 8, "5g":true}');

• JSON 데이터를 문자열로 직접 삽입하는 경우 JSON 형식을 자유롭게 입력할 수 있다.

JSON_OBJECT 함수 사용

INSERT INTO product_json (name, category, attributes) VALUES
('아이폰 15 프로','스마트폰',
JSON_OBJECT( 
	'screen_size', 6.1,
    'storage', 256,
    'color', '블랙 티타늄', 
    'ram', 8, 
    '5g', true
));

• "JSON_OBJECT" 함수를 사용하면 키-값 쌍을 직접 지정할 수 있다.

JSON_ARRAY 함수 사용

INSERT INTO product_json (name, category, attributes) VALUES
('맥북 프로 16', '노트북',
JSON_OBJECT(
'screen_size', 16.2,
'storage', 512,
'color', '스페이스 그레이',
'ram', 18,
'ports', JSON_ARRAY(
	'HDMI', 
    'USB-C',
    'MagSafe', 
    'SD카드'
)));

• 배열을 만들 때는 "JSON_ARRAY" 함수를 사용한다.

데이터 확인

SELECT product_id, name, category, attributes FROM product_json;

쿼리 결과

 

추가 샘플 데이터 입력

INSERT INTO product_json (name, category, attributes) VALUES
('갤럭시 탭 S9', '태블릿', '{"screen_size": 11.0, "storage": 128, "color": "그라파이트", "ram": 8, "5g":false, "s_pen": true}'),
('소니 WH-1000XM5','헤드폰','{"color": "블랙", "noise_canceling": true, "battery_life": 30, "driver_size":30, "bluetooth": "5.2"}'),
('LG 그램 17','노트북','{"screen_size": 17.0, "storage": 512, "color": "화이트", "ram": 16, "weight":1.35, "ports": ["USB-C", "USB-A", "HDMI"]}'),
('에어팟 프로 2','이어폰','{"color": "화이트", "noise_canceling": true, "battery_life": 6,"case_battery": 30, "spatial_audio": true}'),
('갤럭시 워치 6','스마트워치','{"screen_size": 1.5, "color": "실버", "battery_life": 40, "water_resistant":true, "gps": true}');

 

JSON 데이터 조회

전체 JSON 조회

SELECT name, attributes FROM product_json WHERE product_id = 1;

쿼리 결과

 

JSON_EXTRACT 함수로 특정 값 추출

SELECT
    name,
    JSON_EXTRACT(attributes, '$.screen_size') AS screen_size,
    JSON_EXTRACT(attributes, '$.storage') AS storage,
    JSON_EXTRACT(attributes, '$.color') AS color
FROM product_json
WHERE category = '스마트폰';

쿼리 결과

• "JSON_EXTRACT" 함수를 사용하면 JSON에서 특정 경로의 값을 추출할 수 있다.
• 경로는 "$"로 시작하며, "."로 키를 지정한다.

"JSON_EXTRACT"는 저장한 타입을 그대로 꺼낸다. 숫자는 숫자 타입으로, 불리언은 불리언 타입으로 조회한다. 주의할 점은 "JSON_EXTRACT"로 문자열을 추출하면 큰따옴표가 포함된다는 것이다. "JSON_EXTRACT"는 추출한 값의 JSON 데이터 타입(형식)을 그대로 유지하여 반환하기 때문이다. JSON 표준에서 문자열은 반드시 큰따옴표로 감싸져 있어야 하므로, 결과에도 큰따옴표가 포함된다.

 

JSON_QUERY, JSON_EXTRACT

MySQL에서는 표준 SQL과 달리 JSON_QUERY라는 함수명이 존재하지 않는다. 대신 "JSON_EXTRACT"가 그 역할을 수행한다.

 

"->" 연산자 (JSON_EXTRACT 축약형)

SELECT
	name,
	attributes->'$.screen_size' AS screen_size,
	attributes->'$.storage' AS storage,
	attributes->'$.color' AS color
FROM product_json
WHERE category = '스마트폰';

쿼리 결과

 

JSON_VALUE 함수로 특정 값 추출

SELECT
	name,
	JSON_VALUE(attributes, '$.screen_size') AS screen_size,
	JSON_VALUE(attributes, '$.storage') AS storage,
	JSON_VALUE(attributes, '$.color') AS color
FROM product_json
WHERE category =
'스마트폰';

쿼리 결과

• "JSON_VALUE" 함수는 추출한 값에서 따옴표를 제거하고, 별도의 타입을 지정하지 않으면 문자열로 반환한다.
• screen_size, storage, color 모두 문자열 타입으로 반환된다.

 

JSON_VALUE와 반환 타입 지정

"JSON_VALUE"의 강력한 기능 중 하나는 반환되는 데이터의 타입을 우리가 원하는 대로 지정할 수 있다는 점이다. "JSON_VALUE" 함수에 별도의 타입을 지정하지 않으면 문자열로 반환한다. 숫자 연산을 하거나 크기를 비교할 때, 데이터 타입이 맞지 않으면 엉뚱한 결과가 나올 수 있다. 이때 "RETURNING" 절을 사용하여 데이터 타입을 명시적으로 변환해 준다.

SELECT
	name,
	JSON_VALUE(attributes, '$.storage') AS storage_text,
	JSON_VALUE(attributes, '$.storage' RETURNING UNSIGNED) AS storage,
	JSON_VALUE(attributes, '$.screen_size' RETURNING DECIMAL(4,2)) AS
screen_size
FROM product_json
WHERE category = '스마트폰'
ORDER BY storage DESC;

쿼리 결과

• "RETURNING"을 지정하지 않음: 문자로 반환한다.
  • 여기서 반환한 "storage_text"의 256은 숫자가 아니라 문자열 "256"이다.
• "RETURNING UNSIGNED": 추출한 값을 양의 정수(0과 양수)로 변환한다.
  • 이제 "storage"는 문자열 "256"이 아니라 숫자 256이 된다.
• "RETURNING DECIMAL(3,2)": 추출한 값을 소수점 둘째 자리까지 있는 숫자로 변환한다.

이렇게 타입을 지정하면 SQL 내부에서 대소 비교(>, < )를 하거나 정렬(ORDER BY)을 할 때, 문자열 기준이 아닌 숫자 기준으로 정확하게 동작하게 된다.

 

데이터 타입

"JSON_VALUE" 함수의 "RETURNING" 절 뒤에 사용할 수 있는 데이터 타입들은 MySQL의 "CAST()" 함수에서 지원하는 타입들과 거의 동일하다. 실무에서 자주 사용하는 타입들을 정리해두었으니 참고하자.

 

1. 숫자형 (Numeric Types)

타입	설명	사용 예시
UNSIGNED [INTEGER]	양수 정수 (0 포함)	RETURNING UNSIGNED
SIGNED [INTEGER]	음수 포함 정수	RETURNING SIGNED
DECIMAL[(M[,D])]	고정 소수점 (정확한 계산, 금액 등)	RETURNING DECIMAL(10,2)
FLOAT	부동 소수점 (단정밀도)	RETURNING FLOAT
DOUBLE	부동 소수점 (배정밀도)	RETURNING DOUBLE

 

2. 날짜 및 시간형 (Date and Time Types)

타입	설명	사용 예시
DATE	날짜 ("YYYY-MM-DD")	RETURNING DATE
DATETIME	날짜 + 시간	RETURNING DATETIME
TIME	시간 (HH:MM:SS)	RETURNING TIME

 

3. 문자열 및 바이너리 (String and Binary Types)

타입	설명	사용 예시
CHAR[(N)]	고정 길이 문자열	RETURNING CHAR(50)
BINARY[(N)]	이진 문자열 (대소문자 구분 확실)	RETURNING BINARY(16)

 

"->>" 연산자 (따옴표 제거)

앞서 "JSON_VALUE"를 사용하면 따옴표가 제거된 값을 얻을 수 있다고 했다. 하지만 매번 "JSON_VALUE(col, '$.key')"라고 길게 쓰는 것은 번거롭다. 이를 위해 "->>" 연산자가 제공된다. "->>"는 "JSON_VALUE"의 축약형이다. 추출한 값에서 따옴표를 제거하고 문자열로 반환한다. 단, 이 연산자는 타입을 지정(RETURNING)할 수는 없으므로 단순히 문자 타입으로 값을 꺼낼 때 사용한다

SELECT
	name,
	attributes->>'$.screen_size' AS screen_size,
	attributes->>'$.storage' AS storage,
	attributes->>'$.color' AS color
FROM product_json
WHERE category = '스마트폰';

쿼리 결과

• 결과를 보면 color 값에 따옴표가 제거된 것을 확인할 수 있다. 단순 조회가 목적이라면 "->>" 연산자를 가장 많이 사용하게 될 것이다.
• "->>"는 문자열 타입을 반환하므로 숫자나 불리언을 비교해야 한다면 "->" 연산자를 사용해야 한다.

배열 요소 접근

JSON 데이터가 배열(Array) 형태라면 어떻게 접근해야 할까? 프로그래밍 언어와 마찬가지로 인덱스(Index)를 사용한다. 인덱스는 0부터 시작한다.

SELECT
	name,
	attributes->>'$.ports[0]' AS first_port,
	attributes->>'$.ports[1]' AS second_port
FROM product_json
WHERE category = '노트북';

쿼리 결과

 

JSON 조건 검색

숫자 비교

-- 화면 크기가 6인치 이상인 제품 조회
SELECT name, attributes->'$.screen_size' AS screen_size
FROM product_json
WHERE attributes->'$.screen_size' >= 6;

쿼리 결과

문자열 비교

-- 블랙 색상 제품 조회
SELECT name, attributes->>'$.color' AS color
FROM product_json
WHERE attributes->>'$.color' LIKE '%블랙%'

쿼리 결과

불리언 비교

-- 노이즈 캔슬링 지원 제품 조회
SELECT name, category
FROM product_json
WHERE attributes->'$.noise_canceling' = true;

쿼리 결과

 

JSON_CONTAINS로 배열 검색

-- USB-C 포트가 있는 노트북 조회
SELECT name, attributes->>'$.ports' AS ports
FROM product_json
WHERE JSON_CONTAINS(attributes->'$.ports', '"USB-C"');

쿼리 결과

• 배열에 특정 값이 포함되어 있는지 확인할 때는 "JSON_CONTAINS"를 사용한다.
• "JSON_CONTAINS"의 두 번째 인자는 JSON 형식이어야 하므로 문자열은 '"USB-C"'처럼 큰따옴표를 포함해야 한다.

 

JSON_CONTAINS_PATH로 키 존재 확인

 

JSON_CONTAINS_PATH(json_doc, 'one' | 'all', path[, path] ...)

• one : 여러 path 중 하나라도 존재하면 TRUE
• all : 지정한 모든 path가 존재해야 TRUE

-- s_pen 속성이 있는 제품 조회
SELECT name, category, attributes->>'$.s_pen' AS s_pen
FROM product_json
WHERE JSON_CONTAINS_PATH(attributes, 'one', '$.s_pen');

쿼리 결과