[MySQL] MySQL JSON 관련 함수

JSON 데이터

JSON은 JavaScript Object Notaion으로 자바스크립트 언어에서의 객체 표기법을 의미합니다.

자바스크립트 객체 표기법이지만 자바스크립트에 종속되지 않은 독립적인 포맷의 데이터 입니다.

JSON은 표기법이 속성과 데이터를 표현하는데에 있어서 단순하고 사람이 이해하기 쉬우며 한참 대세로 떠오르던 XML보다 표현 데이터도 소모값이 적기 때문에 어플리케이션간 데이터를 교환하기 위한 개방형 표준 포맷이 되었습니다.

거의 모든 언어나 플랫폼에서 JSON을 다루기 위한 방법을 제공하고 있고 제공하지 않더라도 단순 문자열로서 다루기도 편리하다. JSON은 속성(Key)와 값(Value) 의 쌍으로 이루어 구성됩니다.

자세한 사항은 다음 글을 참고합니다. -> JSON이란? JSON 규칙 

쿼리 결과를 JSON으로 변환하기

MySQL에서는 5.7.8 버전부터 JSON을 다루기 위한 함수를 제공합니다.

하나의 인스턴스(ROW)를 JSON으로 변환하기 위해서는 JSON_OBJECT() 함수나 JSON_ARRAY() 함수를 이용할 수 있습니다.

JSON_OBJECT() 함수의 인자로는 JSON_OBJECT(key, value, key, value...) 형식으로 번갈아 가며 지정해줍니다.

다음과 같은 유저 정보 테이블이 있습니다.

SELECT * FROM users;

해당 쿼리 결과를 함수를 이용해 JSON으로 변경해 보겠습니다.

SELECT JSON_OBJECT('name', name, 'age', age, 'country', country) FROM users;

JASON_ARRAY() 함수를 통해 배열로 만드는 경우

SELECT JSON_ARRAY(name, age, country) FROM users;

JSON 데이터를 다루는 함수들

다음의 쿼리 예제를 바탕으로 JSON 관련 함수들을 설명하도록 하겠습니다.

더욱 자세한 정보 및 함수들은 다음의 링크를 참고합니다.

https://dev.mysql.com/doc/refman/5.7/en/json-functions.html

 

SET @json = '{
                "users" : [
                        {"name" : "Kim", "age" : 26, "country" : "kr"},
                        {"name" : "Lee", "age" : 21, "country" : "usa"},
                        {"name" : "Park", "age" : 18, "country" : "jp"},
                        {"name" : "Moon", "age" : 37, "country" : "cn"},
                        {"name" : "Ryu", "age" : 33, "country" : "kr"}
                ]
             }'; -- JSON 형식 문자열을 @json 변수에 저장
            
SELECT JSON_VALID(@json); -- JSON 포맷 검증
SELECT JSON_SEARCH(@json, 'one', 'kr'); -- JSON에서 조건에 맞는 최초 검색 위치 리턴
SELECT JSON_SEARCH(@json, 'all', 'kr'); -- JSON에서 조건에 맞는 모든 모든 위치 리턴
SELECT JSON_EXTRACT(@json, '$.users[1].name'); -- 두 번째 유저의 name 값 리턴
SELECT JSON_INSERT(@json, '$.users[0].hobby', 'cycle'); -- 첫 번째 유저의 hobby 속성을 생성 후 cycle 저장
SELECT JSON_REPLACE(@json, '$.users[0].name', 'Hong'); -- 첫 번째 유저의 name을 Hong으로 변경
SELECT JSON_REMOVE(@json, '$.users[0]');    -- 첫 번째 유저 정보를 삭제

JSON 규칙을 잘 따르는지 데이터를 검증하며 유효한 경우 1을 유효하지 않은 경우 0을 리턴합니다.

SELECT JSON_VALID(@json); -- 1

JSON 데이터에서 세 번째 인자로 넘긴 데이터를 기준으로 검색하고 최초로 찾은 위치를 반환합니다.

'kr'이라는 데이터는 첫 번째 유저의 country 속성에서 발견했습니다.

또한 두 번째 인자로 'one'을 넘기는 경우 조건에 맞는 최초의 데이터 하나를 검색합니다.

SELECT JSON_SEARCH(@json, 'one', 'kr'); -- "$.users[0].country"

두 번째 조건이 'one'이 아닌 'all' 인경우 검색된 모든 위치를 JSON 배열 형태로 반환합니다.

SELECT JSON_SEARCH(@json, 'all', 'kr'); -- ["$.users[0].country", "$.users[4].country"]

지정한 값을 추출합니다.

첫 번째 유저의 name 속성 값

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

지정한 위치에 속성과 값을 추가합니다.

첫 번째 유저의 hobby 속성에 cycle을 추가

SELECT JSON_INSERT(@json, '$.users[0].hobby', 'cycle'); -- {"users": [{"age": 26, "name": "Kim", "hobby": "cycle", "country": "kr"},....생략...

지정한 위치에 데이터를 수정합니다.

첫 번째 유저의 name 값을 Hong으로 수정.

SELECT JSON_REPLACE(@json, '$.users[0].name', 'Hong'); -- {"users": [{"age": 26, "name": "Hong", "country": "kr"},....생략...

지정한 위치의 데이터를 제거합니다.

첫 번째 유저 데이터를 제거.

SELECT JSON_REMOVE(@json, '$.users[0]'); -- {"users": [{"age": 21, "name": "Lee", "country": "usa"},....생략...