[MySQL] JSON 데이터 다루기

안녕하세요.

데이터 포맷으로 많이들 사용되고 있는 JSON 데이터를 MySQL로 다루는 방법에 대해 알아보겠습니다.

 

 MySQL JSON

▣ JSON이란? (JavaScript Object Notation)

속성-값 쌍(attribute–value pairs), 배열 자료형(array data types) 또는 기타 모든 시리얼화 가능한 값(serializable value) 또는 "키-값 쌍"으로 이루어진 데이터 오브젝트를 전달하기 위해 인간이 읽을 수 있는 텍스트를 사용하는 개방형 표준 포맷

- 대부분의 프로그래밍 언어에서 다뤄지며 데이터 전송 등 다양하게 사용됨.

 

: JSON 형식 예시

{   "movie" : "아바타: 물의 길",
    "open_date" : "2022-12-14",
    "director" : "제임스 카메론",
    "actor" : ["조 샐다나", "샘 워싱턴"]
}

- JSON 데이터는 {"key" : value} 형식으로, key 값은 반드시 ""가 포함된 String이고 value 값은 기본 자료형뿐만 아니라 객체, 배열 모두 가능함.

 

▣ MySQL JSON Function

- [MySQL] JSON 함수 모음

- 위는 MySQL에 내장된 JSON 함수 리스트이며, 다음은 이중 자주 사용되는 MySQL JSON 관련 함수 리스트임.

Name	Description
JSON_VALID(val)	JSON 형식이 맞다면 1 아니면 0 반환
JSON_SEARCH( json, one or all, search_str)	search_str 파라미터의 문자열 위치를 반환 one은 처음으로 매치되는 하나만, all은 매치되는 모든 것을 반환
JSON_EXTRACT(json, path)	path 위치에 해당되는 값 반환
JSON_INSERT(json, path, value)	새로운 속성(path)를 추가하고 value 값을 부여함
JSON_REPLACE(json, path, value)	path 위치에 해당되는 값을 value로 대체함
JSON_KEYS(json[, path])	JSON 데이터의 KEY 값 반환 (위치 설정 가능)
JSON_REMOVE(json, path)	지정된 path 위치의 항목을 제거함

 

: 실전 활용 예시

@js_movie
{ "movies" : [ {"movie" : "아바타, 물의 길", "director" : "제임스 카메론"}, {"movie" : "올빼미", "director" : "안태진"}, {"movie" : "영웅", "director" : "윤제균"}, {"movie" : "극한직업", "director" : "이병헌"} ] }

- js_movie 라는 json 데이터로 JSON 함수를 실전 활용해보기

SET @js_movie = '{
                "movies" : [
                        {"movie" : "아바타, 물의 길", "director" : "제임스 카메론"},
                        {"movie" : "올빼미", "director" : "안태진"},
                        {"movie" : "영웅", "director" : "윤제균"},
                        {"movie" : "극한직업", "director" : "이병헌"}
                ]
             }';
             
-- JSON 데이터 확인
SELECT JSON_VALID(@js_movie);
-- 결과: 1

-- JSON 데이터 찾기
SELECT JSON_SEARCH(@js_movie, 'one','올빼미');
-- 결과: $.js_movie[1].movie

-- JSON 데이터 추출
SELECT JSON_EXTRACT(@js_movie, '$.movies[0].movie');
-- 결과: 아바타, 물의 길

-- JSON 데이터 삽입
SELECT JSON_EXTRACT(@js_movie, '$.movies[0].numbers', '1,000만');
-- 결과: $.movies[0] : {"movie" : "아바타, 물의 길", "director" : "제임스 카메론","numbers" : '1,000만'}

-- JSON 데이터 대체
SELECT JSON_EXTRACT(@js_movie, '$.movies[0].movie', '아바타3');
-- 결과: $.movies[0] : {"movie" : "아바타3", "director" : "제임스 카메론"}

-- JSON 데이터 KEY 값
SELECT JSON_KEYS(@js_movie, '$.movies[0]');
-- 결과: ["movie", "director"]

-- JSON 데이터 제거
SELECT JSON_REMOVE(@js_movie, '$.movies[0]');
-- 결과: @js_movie 데이터 1번째 항목 제거됨.