MySQL 에서 JSON Data사용하기

MySQL 5.7.8 부터 DBMS 차원에서 JSON 데이타를 지원합니다. JSON 을 다루는 방법을 기록 관리 차원에서 정리해 봅니다.

먼저 JSON 데이타 처리 테스트를 위한 Sample 테이블을 생성합니다.

CREATE TABLE employees (
	id integer  AUTO_INCREMENT primary key,
	name VARCHAR(200),
	profile JSON
);

JSON 데이타 입력

DB 에 insert 시 JSON 형식으로 입력하면 나중에 json path 나 json 관련 함수를 사용해서 처리할 수 있습니다.

문자열로 JSON 만들어서 입력

The JSON Data Type - https://dev.mysql.com/doc/refman/5.7/en/json.html

JSON Object를 입력할 경우 key → value 데이타는 아래와 같이 { 안에 key 와 value 를 넣어서 입력합니다.

insert into employees(name, profile) values('홍길동', '{ "age" : 30, "gender" : "man", "부서": "개발" }');
insert into employees(name, profile) values('김철수', '{ "age" : 43, "gender" : "man", "부서": "재무" }');
insert into employees(name, profile) values('박영희', '{ "age" : 37, "gender" : "woman", "부서": "회계" }');

배열로 입력

insert into employees(name, profile) values('김갑수', '[35, "man", "인사"]');

JSON_ 함수 사용

문자열로 JSON 을 입력할 경우 실수할 여지가 많으므로 MySQL 이 제공하는 JSON 관련 함수를 사용하여 처리하는 게 좋습니다.

JSON_OBJECT

JSON Object 입력을 쉽게 해주는 함수로 JSON_OBJECT 함수의 파라미터로 name1, value1, name2, value2 처럼 key: value 쌍을 맞춰서 호출하며 가독성을 위해 파라미터 쌍마다 개행을 하는 것을 추천합니다.

insert into employees(name, profile) values('신상일', json_object(
    'age', 28, 
    'gender', 'man', 
    '부서', '연구'
));

JSON_ARRAY

배열 입력시 유용하며 다음은 종업원 정보중에 소지한 자격증을 배열로 입력하는 예제입니다.

insert into employees(name, profile) values('은연수', json_object(
    'age', 29,
    'gender', 'woman',
    '부서', '개발',
    '자격증', json_array('CISA', 'PMP', 'CISSP')
    ));

JSON_QUOTE

문자열에 특수 문자가 있을 경우 처리합니다. 다음은 'n 을 입력하는 예제입니다.

SELECT JSON_QUOTE('Scott\'n tiger'), JSON_QUOTE('"null');

가져오기

Json 을 잘 처리하려면 JsonPath 기반 지식 필요합니다.

주의 사항

JSON 에 있는 데이타는 한글 데이타를 추출해서 비교할 경우 key 를 '로 전체를 묶고 한글 컬럼명을 " 로 감싸줍니다.

아래는 부서가 개발인 직원 정보를 출력하는 예제입니다.

select id,name,json_extract(profile, '$."부서"')
 from employees where json_extract(profile, '$."부서"') = '개발';

이중 따옴표(double Quote) 없애기

아래 쿼리를 실행하면 결과가 0 이 나오며 이유는 json_extract 가 결과에 "" 를 붙이기 때문입니다.

select id,name,json_extract(profile, '$."부서"')
 from employees where json_extract(profile, '$."부서"') like '개%';

즉 아래와 같은 구문을 평가하므로 결과가 0 이 나오게 됩니다.

select '"개발"' like '개%';

이를 해결하려면 Stack Overflow 답변에 있는대로 MySQL 의 "unquotes the extracted result" 연산자인 ->> 를 사용해서 Quote 를 제거해 주면 됩니다.

select id,name, profile ->> '$."부서"', json_extract(profile, '$."부서"')
 from employees where profile ->> '$."부서"' like '개%';

JSON_Extract

컬럼에서 JSON 데이타를 추출하며 JSON Path 문법을 사용합니다.

아래는 종업원의 이름과 부서를 표시하는 예제입니다.

select id, name, json_extract(profile, '$.dept') from employees;

다음은 나이가 35 이상인 종업원의 이름과 나이를 표시합니다.

select id, name, json_extract(profile, '$.age') from employees where json_extract(profile, '$.age') >= 35;

JSON_Replace

JSON 컬럼에서 값을 치환해서 리턴하는 함수입니다..

다음은 age 필드의 값을 30으로 변경하는 예제입니다.

select id, name, json_replace(profile, '$.age', 30) from employees;

json_replace 도 여러 컬럼을 수정할 수 있으며 key, value 쌍으로 파라미터를 넘기면 됩니다. 가독성을 위해 json_object 처럼 파라미터 쌍마다 개행을 하는 것이 좋습니다.

다음은 age와 gender 필드의 값을 변경해서 표시하는 예제입니다.

select id, name, json_replace(profile,
    '$.age', 30,
    '$.gender', '남녀'
    ) from employees;

모든 컬럼값을 동일하게 변경하는 것은 보통 유용하지가 않으므로 기존 컬럼 값을 수정할 경우 json_extract 와 json_replace 를 같이 사용해서 값을 변경합니다.

다음은 모든 직원의 age 필드 값에 1을 더해서 가져옵니다.

select id, name, json_replace(profile, '$.age', json_extract(profile, '$.age') + 1) from employees;

다음 쿼리는 전체 직원들의 나이를 1 을 더하고 부서명에 "부" 를 추가하는 예제입니다.

update

Update
  employees as e
  inner join (
    select id, JSON_REPLACE(profile,
        '$.age', profile ->> '$.age' + 1 ,
        '$."부서"', concat(profile ->> '$."부서"', '부')
    ) as newProfile
    from employees as p
  ) as A on e.id = A.id
set e.profile = A.newProfile;

같이 보기

JSONPath 사용법

DBMS & NoSQL