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

JSON 데이터 수정

JSON_SET으로 값 추가/수정

JSON_SET(json_doc, path, value [, path, value] ...)

• "JSON_SET"은 키가 있으면 값을 수정하고, 없으면 새로 추가한다.
• json_doc: 대상 JSON 컬럼
• path: JSON 경로 ($.key, $.obj.key 등)
• value: 설정할 값 (문자, 숫자, boolean, JSON 등)

-- 갤럭시 S24에 새로운 속성 추가
UPDATE product_json
SET attributes = JSON_SET(attributes, '$.wireless_charging', true, '$.storage', 1024)
WHERE product_id = 1;

-- 결과 확인
SELECT name, attributes FROM product_json WHERE product_id = 1;

쿼리 결과

• 기존에 없는 wireless_charging 속성과 값을 추가하고, 기존에 있는 storage 속성의 값을 "256" => "1024"로 변경한다.

JSON_INSERT로 값 추가만

-- 이미 있는 color는 변경되지 않고, 없는 fast_charging만 추가됨
UPDATE product_json
SET attributes = JSON_INSERT(attributes, '$.color','새로운 색상', '$.fast_charging', true)
WHERE product_id = 1;

-- 결과 확인
SELECT name, attributes->>'$.color' AS color, attributes->>'$.fast_charging'
AS fast_charging
FROM product_json WHERE product_id = 1;

쿼리 결과

• "JSON_INSERT"는 키가 없을 때만 추가한다. 이미 있는 키는 수정하지 않는다.
• "color"는 기존 값 "팬텀 블랙"이 유지되고, "fast_charging"만 새로 추가되었다.

JSON_REPLACE로 값 수정만

-- 있는 storage는 변경되고, 없는 new_feature는 추가되지 않음
UPDATE product_json
SET attributes = JSON_REPLACE(attributes, '$.storage', 256, '$.new_feature', true)
WHERE product_id = 1;

-- 결과 확인
SELECT name,
attributes->>'$.storage' AS storage,
attributes->>'$.new_feature' AS new_feature
FROM product_json WHERE product_id = 1;

쿼리 결과

• "JSON_REPLACE"는 키가 있을 때만 수정한다. 없는 키는 추가하지 않는다.
• "storage"는 "256"로 변경되었지만, "new_feature"는 추가되지 않았다.

JSON_REMOVE로 값 삭제

-- fast_charging 속성 삭제
UPDATE product_json
SET attributes = JSON_REMOVE(attributes, '$.fast_charging', '$.wireless_charging')
WHERE product_id = 1;

-- 결과 확인
SELECT name, attributes FROM product_json WHERE product_id = 1;

쿼리 결과

• "fast_charging", "wireless_charging"이 삭제되었다

함수	차이
JSON_SET	있으면 수정, 없으면 추가
JSON_INSERT	없을 때만 추가
JSON_REPLACE	있을 때만 수정
JSON_REMOVE	삭제

 

배열에 요소 추가

-- 맥북에 새로운 포트 추가
UPDATE product_json
SET attributes = JSON_ARRAY_APPEND(attributes, '$.ports', '3.5mm 오디오')
WHERE product_id = 3;

-- 결과 확인
SELECT name, attributes->>'$.ports' AS ports FROM product_json WHERE
product_id = 3;

쿼리 결과

• "JSON_ARRAY_APPEND"를 사용하면 배열에 요소를 추가할 수 있다.

JSON 유틸리티 함수

JSON_KEYS - 키 목록 조회

SELECT name, JSON_KEYS(attributes) AS attribute_keys
FROM product_json
WHERE product_id = 1;

쿼리 결과

JSON_LENGTH - 요소 개수

-- 속성 개수 확인
SELECT name, JSON_LENGTH(attributes) AS attr_count
FROM product_json
LIMIT 5;

쿼리 결과

-- 배열 길이 확인
SELECT name, JSON_LENGTH(attributes->'$.ports') AS port_count
FROM product_json
WHERE category = '노트북';

쿼리 결과

 

JSON_TYPE - 타입 확인

SELECT
    name,
    JSON_TYPE(attributes->'$.screen_size') AS screen_type,
    JSON_TYPE(attributes->'$.color') AS color_type,
    JSON_TYPE(attributes->'$."5g"') AS `5g_type`
FROM product_json
WHERE product_id = 1;

쿼리 결과

• "5g"에 따옴표로 감싼 것을 주의하자.
• "5g_type" : 백틱이 추가되어 있다. MySQL에서 숫자로 시작하는 식별자는 백틱이 필요하다.
• $.key 형태의 key는 문자로 시작하고 문자/숫자/_만 허용한다.
  • 숫자로 시작하는 키 또는 특수문자가 포함된 키는 반드시 따옴표로 감싸야 한다.
  • JSON의 키는 가급적 영문 알파벳 문자로 시작하는 것이 좋다.

JSON_VALID - 유효성 검사

SELECT
    JSON_VALID('{"name": "test"}') AS valid_json,
    JSON_VALID('{name: test}') AS invalid_json,
    JSON_VALID('not json at all') AS not_json;

쿼리 결과

 

JSON_PRETTY - 보기 좋게 출력

SELECT JSON_PRETTY(attributes) AS formatted_json
FROM product_json
WHERE product_id = 1;

쿼리 결과

 

정리

JSON 문법 핵심은 다음과 같다.

• 객체 "{}"와 배열 "[]" 두 가지 기본 구조
• 6가지 데이터 타입: 문자열, 숫자, 불리언, null, 객체, 배열
• 키는 반드시 큰따옴표로 감싸야 함

MySQL JSON 핵심 함수는 다음과 같다.

• "JSON_OBJECT", "JSON_ARRAY" : JSON 생성
• "->", "->>" : JSON 값 추출 ("->"는 JSON 타입, "->>"는 문자열)
• "JSON_SET" , "JSON_INSERT" , "JSON_REPLACE" : JSON 수정
• "JSON_REMOVE" : JSON 값 삭제
• "JSON_CONTAINS" , "JSON_CONTAINS_PATH" : 포함 여부 확인