개요
최근 MySQL에서 JSON 데이터를 지원하고 있다는 것을 알게 되었다. 간단히 읽고 쓰는 방법을 확인해 보자.
JSON 타입
MySQL 데이터베이스의 테이블에서 JSON 데이터를 다루기 위해서는 테이블 컬럼의 데이터형이 JSON이어야 한다.
CREATE TABLE employees (
id integer AUTO_INCREMENT primary key,
name VARCHAR(200),
profile JSON
);
INSERT
JSON 데이터는 문자열 또는 JSON_OBJECT를 사용하여 입력할 수 있다.
1. 문자열로 입력하기
INSERT INTO employees(name, profile)
VALUES ('홍길동', '{ "age" : 30, "gender" : "man", "부서": "개발" }');
2. JSON_OBJECT로 입력하기
문자열로 입력할 때는 실수할 여지가 많으므로 json_object를 사용하여 입력하는 것이 권장된다. json_object를 사용할 때는 key와 value를 쉼표로 구분하여 나열한다. 가독성을 위해 key:value 쌍 사이에 개행을 두는 것이 좋다.
INSERT INTO employees(name, profile)
VALUES ('신상일', json_object(
'age', 28,
'gender', 'man',
'부서', '연구'
));
실행 결과
| id | name | profile |
| --- | ---- | ---------------------------------------- |
| 1 | 홍길동 | {"age": 30, "gender": "man", "부서": "개발"} |
| 2 | 신상일 | {"age": 28, "gender": "man", "부서": "연구"} |
Array INSERT
value가 list인 경우에는 JSON_ARRAY를 사용하여 값을 추가한다.
INSERT INTO employees(name, profile)
VALUES ('은연수', json_object(
'age', 29,
'gender', 'woman',
'부서', '개발',
'자격증', json_array('CISA', 'PMP', 'CISSP')
));
실행 결과
| id | name | profile |
| --- | ---- | --------------------------------------------------------------------------- | |
| 3 | 은연수 | {"age": 29, "gender": "woman", "부서": "개발", "자격증": ["CISA", "PMP", "CISSP"]} |
JSON_EXTRACT
JSON_EXTRACT 함수는 JSON PATH 문법을 사용하여 컬럼에서 JSON 데이터를 추출한다.
아래 쿼리는 employees 테이블에서 name과 age를 조회한 것이다.
SELECT name
, json_extract(profile, '$.age') AS AGE
FROM employees
;
실행 결과
| name | AGE |
| ---- | --- |
| 홍길동 | 30 |
| 신상일 | 28 |
| 은연수 | 29 |
JSON_EXTRAT 함수는 WHERE 절에서도 사용할 수 있다. 아래 쿼리는 employees 테이블에서 부서가 개발인 직원의 name과 부서, age를 조회한 것이다.
SELECT name
, json_extract(profile, '$."부서"') AS DEPT
, json_extract(profile, '$.age') AS AGE
FROM employees
WHERE TRUE
AND json_extract(profile, '$."부서"') = '개발'
;
실행 결과
| name | DEPT | AGE |
| ---- | ---- | --- |
| 홍길동 | "개발" | 30 |
| 은연수 | "개발" | 29 |
참고 문서
https://dev.mysql.com/doc/refman/5.7/en/json.html