MySQL - JSON 데이터 사용

JSON 데이터란 ?

JSON(JavaScript Object Notation)은 현대의 웹과 모바일 애플리케이션 등과 데이터를 교환하기 위한 개방형 표준 포맷을 말합니다. JSON은 JSP(JavaScript)에서 파생되었지만 특정한 언어에 종속되어 있지 않은 독립적인 데이터 포맷입니다. 대부분의 프로그래밍 언어에서 쉽게 읽고 쓸 수 있고 용량이 작아서 최근에는 XML을 대체해서 데이터 전송에 많이 사용합니다.

 

즉, 간략히 설명하자면 JSON은 데이터 포맷(형식) 중 하나일 뿐이며, 주로 서버와 클라이언트 간의 교류에서 많이 사용됩니다.

 

JSON 형식

{
    "id" : "kim0123" ,
    "name" : "suhyeon" ,
    "location" : "LA"
    "phone_number" : "01012345678"
    "hobby" : ["football", "sing"]
}

JSON은 { "key" : value } 형식입니다.  key(속성)는 반드시 String이여야 하며, value(값)는 기본 자료형이나 객체, 배열 모두 가능합니다. 각 쌍은 쉼표(,)로 구분합니다. "hobby"에 값으로 대괄호가 나온 것은 배열임을 의미합니다.

 

객체와 배열을 적절하게 이용해서 JSON으로 테이블을 구현하여 데이터 통신에 이용합니다.

 

MySQL의 JSON 관련 내장 함수

MySQL의 테이블을 JSON 데이터로 변환하는 예제입니다.

 

memberID	memberName	memberAddress
Han	한주연	인천 남구 주안동
Jee	지운이	서울 은평구 증산동
Sang	상길이	경기 성남시 분당구

 

membertbl이라는 이름의 테이블이 있다고 가정합니다.

SELECT JSON_OBJECT('memberId', memberId, 'memberName', memberName, 'memberAddress', memberAddress)
AS 'JSON Data'
FROM membertbl;

위와 같이 JSON_OBJECT()를 사용하면 테이블을 JSON 데이터로 읽어 올 수 있습니다. 배열로 읽어 오길 원하면 JSON_ARRAY()를 사용하면 됩니다.

 

JSON Data

{"memberId": "Han", "memberName": "한주연", "memberAddress": "인천 남구 주안동"}

{"memberId": "Jee", "memberName": "지운이", "memberAddress": "서울 은평구 증산동"}

{"memberId": "Sang", "memberName": "상길이", "memberAddress": "경기 성남시 분당구"}

 

결과로 JSON 형식의 데이터가 저장된 테이블입니다. 이렇게 MYSQL 테이블을 JSON 데이터로 변환할 수 있습니다.

 

다음은 JSON 데이터를 다루는 함수에 대해 알아보겠습니다.

SET @json = '{
                "users" : [
                        {"name" : "lee", "age" : 25},
                        {"name" : "kim", "age" : 24},
                        {"name" : "Park", "age" : 13},
                        {"name" : "wang", "age" : 31},
                        {"name" : "jeong", "age" : 38}
                ]
             }'; 

@json 변수에 JSON 데이터를 저장했습니다.

SELECT JSON_VALID(@json);

JSON_VALID()는 문자열이 JSON 형식인지 검증하는 함수입니다. JSON 형식으로 적절하면 1, 그렇지 않으면 2를 반환합니다. 실행하면 1을 반환합니다.

SELECT JSON_SEARCH(@json, 'one', 'kim');

JSON_SEARCH()는 세 번째 파라미터의 문자열의 위치를 반환합니다. 두 번째 파라미터는 'one'과 'all'이 올 수 있는데, 'one'은 처음으로 매치되는 하나만, 'all'은 매치되는 모든 것을 반환합니다. 실행하면 "$.users[1].name"을 반환합니다.

SELECT JSON_EXTRACT(@json, '$.users[1].name'); 

JSON_EXTRACT()는 위치에 해당하는 값을 반환합니다. 실행하면 "kim"을 반환합니다.

SELECT JSON_INSERT(@json, '$.users[0].height', 175);

JSON_INSERT()는 새로운 속성(두 번째 파라미터)를 추가하고, 값(세 번째 파라미터)를 부여합니다. 실행하면 첫 번째 JSON 데이터가 {"age": 25, "name": "lee", "height": 175}로 "height"속성값이 추가되고 값으로 175가 부여됩니다.

SELECT JSON_REPLACE(@json, '$.users[0].name', 'song');

JSON_REPLACE()는 두 번째 파라미터의 위치의 값을 세 번째 데이터로 변경합니다. 실행하면 첫 번째 JSON 데이터가 {"age": 25, "name": "song"}로 name 값이 "song"으로 변경됩니다. 

SELECT JSON_REMOVE(@json, '$.users[0]');

JSON_REMOVE()는 지정된 항목을 제거합니다. 실행하면 첫 번째 JSON 데이터가 삭제되고, name이 "kim"인 데이터부터 시작합니다.