MySQL JSON 데이터 다루기

INSERT를 위한 함수

- JSON_OBJECT()

: 객체로 입력

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')
    ));

 

 

 

 

 

 

UPDATE를 위한 함수

- JSON_SET()

: 기존 값을 업데이트 하는데, 존재하지 않는 경우 추가

UPDATE table_name SET json_col = JSON_SET(json_col, '$.name', UPPER(json_col->>'$.name'))

 

- JSON_REPLACE()

: 기존의 값만 수정

UPDATE table_name SET json_col = JSON_REPLACE(JSON_REMOVE(json_col, '$.address'))

 

- JSON_REMOVE()

: 기존 값을 삭제. 삭제된 값은 NULL이 됨

UPDATE table_name SET json_col = JSON_REMOVE(json_col, '$.address')

 

 

 

 

 

 

 

SELECT, WHERE 등 조회를 위한 함수

- JSON_EXTRACT()

: 컬럼에서 JSON 데이터를 추출

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

 

- JSON_ARRAY()

: 컬럼에서 배열 데이터를 추출

SELECT JSON_ARRAY(name, price, category) FROM goods;

 

- JSON_OBJECT()

: 일반 데이터를 JSON 형태로 출력

SELECT JSON_OBJECT('name',name, 'category',category, 'price', price) 
FROM goods;

 

- JSON_SET()

: 출력되는 JSON 문서에 속성 값을 추가하거나 변환하여 반환

select id, name, JSON_SET(json_col, '$.name', UPPER(json_col->>'$.name')) from employees;

 

- JSON_REPLACE()

: JSON 컬럼에서 값을 치환하여 반환. 존재하지 않는 속성이면 무시

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

 

- JSON_INSERT()

: 값 변경 없이 SELECT 하면서 값을 추가

SELECT JSON_INSERT(@j, '$.a', 10, '$.c', '[treu, false]');

 

 

※ JSON Path

JSONPath 사용법

 

 

 

그외 MySQL에서 지원하는 JSON 함수

https://dev.mysql.com/doc/refman/8.0/en/json-function-reference.html

 

 

 

 

JSON 컬럼에 인덱싱

CREATE TABLE users (
    id    INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(64) NOT NULL,
    info JSON,                                          
    age_virtual INT GENERATED ALWAYS AS (info->'$.age')          
);

- GENERATED ALWAYS AS

: GENERATED ALWAYS 라는 문구는 선택사항으로, 테이블 열이 generated된 컬럼임을 명시한다는 의미이다

: AS 문은 필수로, 대상 JSON 컬럼에서 가리키는 속성을 명시한다

 

- 인덱싱

CREATE INDEX age_idx ON users(age_virtual);

: age_virtual 컬럼은 info의 age 속성을 가리키며, 해당 컬럼에 인덱스를 생성함으로써 JSON 데이터에 인덱스를 걸 수 있음

 

 

 

 

 

 

 

Reference

MySQL 에서 JSON Data사용하기

Mysql JSON - SELECT, INSERT, UPDATE, 색인