Page tree

JSON 데이타 입력

Sample 테이블

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


문자열로 JSON 만들어서 입력


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 관련 함수를 사용하여 처리(https://dev.mysql.com/doc/refman/5.7/en/json-function-reference.html)


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

문자열에 특수 문자가 있을 경우 처리

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


가져오기

Json 을 잘 처리하려면 JsonPath 기반 지식 필요


주의 사항

한글 컬럼일 경우 ' 로 전체를 묶고 한글 컬럼명을 " 로 감싸줌.(https://stackoverflow.com/questions/35735454/mysql-json-extract-path-expression-error)


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

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;



같이 보기

Ref