MySQL에서 JSON 데이터 사용하기

샘플 테이블 작성

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

 

 

 

 

 

INSERT

insert into employees(name, profile) values('홍길동', '{ "age" : 30, "gender" : "man", "부서": "개발" }');


## json_object 함수
## key, value 쌍을 맞춰서 호출
insert into employees(name, profile) values('신상일', json_object(
    'age', 28, 
    'gender', 'man', 
    '부서', '연구'
));


## json_array 함수
## JSON 객체 내 배열 입력
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');

 

 

 

 

 

SELECT

## json_extract
## json 컬럼에서 특정 프로퍼티만 출력
select id,name,json_extract(profile, '$."부서"')
from employees where json_extract(profile, '$."부서"') = '개발'; 

 
 ## json_replace
 ## json 컬럼에서 값을 치환하여 반환
 ## age 필드 값을 30으로, gender를 남녀로 출력
 select id, name, json_replace(profile,
    '$.age', 30,
    '$.gender', '남녀'
 ) from employees;


## 모든 필드 값에 +1 출력
select id, name, 
json_replace(profile, '$.age', json_extract(profile, '$.age') + 1) 
from employees;

 

 

 

 

 

UPDATE

## json_replace
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;


## json_set
## 기존 값을 업데이트하고 존재하지 않을 경우에는 추가
update employees set profile = json_set(profile, '$'.age', 30);


## json_remove
## 값을 삭제. 값은 NULL이 됨
update employees set profile = json_remove(profile, '$'.age');

 

 

 

 

 

 

Reference

MySQL 에서 JSON Data사용하기

 

Mysql JSON - SELECT, INSERT, UPDATE, 색인