[바미] MySQL JSON 데이터 조회

들어가기전에..

MySQL은 5.7 버전부터 JSON 데이터를 효율적으로 저장하고 조회할 수 있는 기능을 제공하고 있습니다. 

MySQL에서 JSON 데이터를 어떻게 저장하고 조회하는 지 살펴보도록 하겠습니다.

---

MySQL에서 JSON 데이터 타입?

JSON(JavaScript Object Notation)은 가볍고 사람이 읽기 쉬운 데이터 교환 형식입니다. 

MySQL에서는 JSON 데이터 타입을 제공하여 JSON 형식의 데이터를 효율적으로 저장하고 관리할 수 있죠.

 

구조회된 데이터를 유연하게 저장이 가능하고, 스키마 변경없이 필드를 추가할 수 있고, 복잡한 데이터 구조도 저장이 가능합니다.

---

JSON 데이터 저장

우선 JSON 데이터를 저장할 테이블을 만들어보겠습니다.

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    info JSON
);

데이터도 삽입해보죠.

INSERT INTO users (info) VALUES
('{"name": "홍길동", "age": 30, "address": {"city": "서울", "zip": "12345"}}'),
('{"name": "김철수", "age": 25, "address": {"city": "부산", "zip": "54321"}}');

---

JSON 데이터 조회

JSON 데이터는 일반적인 SQL 문법으로는 직접 조회하기 어렵습니다. MySQL에서는 이를 위해 특별한 함수와 연산자를 제공합니다.

JSON_EXTRACT 함수

JSON_EXTRACT() 함수는 JSON 데이터에서 특정 경로의 값을 추출하는 함수입니다.

JSON_EXTRACT(json_doc, path)

 

• json_doc: JSON 데이터가 저장된 컬럼 또는 변수
• path: JSON 경로 표현식

위의 구조로 이루어져 있고, 사용 할 땐 아래와 같이 사용하면 됩니다.

SELECT JSON_EXTRACT(info, '$.name') FROM users;

 

info 컬럼에서 name 필드의 값을 추출하는 쿼리가 됩니다.

-> 및 ->> 연산자 사용

 

• ->: JSON_EXTRACT()와 동일하게 JSON 값을 추출합니다.
• ->>: 추출된 값을 문자열로 반환합니다.

사용할 땐 아래와 같이 사용할 수 있습니다.

-- JSON 값을 그대로 반환
SELECT info->'$.name' FROM users;

-- 문자열로 반환
SELECT info->>'$.name' FROM users;

 

---

JSON 데이터 업데이트

JSON 데이터를 업데이트할 때는 JSON_SET, JSON_REPLACE, JSON_REMOVE 등의 함수를 사용할 수 있습니다.

 

아래는 예시 쿼리입니다.

-- age 값을 31로 업데이트
UPDATE users
SET info = JSON_SET(info, '$.age', 31)
WHERE id = 1

---

JSON 데이터 인덱싱

JSON 데이터를 효율적으로 조회하기 위해서는 인덱스를 생성하는 것이 좋은데 MySQL 8.0부터는 생성된 가상 컬럼(generated virtual column)을 사용하여 JSON 필드에 인덱스를 걸 수 있습니다.

-- 가상 컬럼 생성
ALTER TABLE users
ADD COLUMN name VARCHAR(50) GENERATED ALWAYS AS (info->>'$.name'),
ADD INDEX idx_name (name);

---

다양한 예제

전체 데이터 조회

SELECT * FROM users;

결과

id	info
1	{"name": "홍길동", "age": 31, "address": {"city": "서울", "zip": "12345"}}
2	{"name": "김철수", "age": 25, "address": {"city": "부산", "zip": "54321"}}

특정 필드 조회

이름(name)만 조회할 때

SELECT info->>'$.name' AS name FROM users;

결과

name

홍길동

김철수

나이(age)만 조회

SELECT info->>'$.age' AS age FROM users;

결과

age

31

25

중첩된 필드 조회

주소(city) 조회

SELECT info->>'$.address.city' AS city FROM users;

결과

city

서울

부산

조건에 따른 데이터 조회

나이가 30 이상인 사용자 조회

SELECT info->>'$.name' AS name, info->>'$.age' AS age
FROM users
WHERE CAST(info->>'$.age' AS UNSIGNED) >= 30;

결과

name	age
홍길동	31

서울에 거주하는 사용자 조회

SELECT info->>'$.name' AS name, info->>'$.address.city' AS city
FROM users
WHERE info->>'$.address.city' = '서울';

결과

name	city
홍길동	서울

배열 데이터 조회

만약 JSON 데이터에 배열이 포함되어 있다면 아래와 같이 조회하시면 됩니다.

 

아래와 같이 데이터를 추가해주세요.

INSERT INTO users (info) VALUES
('{"name": "이영희", "age": 28, "hobbies": ["독서", "여행", "요리"]}');

 

취미(hobbies) 조회

SELECT info->>'$.hobbies[0]' AS hobby1,
       info->>'$.hobbies[1]' AS hobby2,
       info->>'$.hobbies[2]' AS hobby3
FROM users
WHERE info->>'$.name' = '이영희';

결과

hobby1	hobby2	hobby3
독서	여행	요리

취미 중 '여행'이 포함된 사용자 조회

SELECT info->>'$.name' AS name
FROM users
WHERE JSON_CONTAINS(info->'$.hobbies', '"여행"');

결과

name

이영희

---

마치며..

지금까지 MySQL에서 JSON 데이터를 저장하고 조회하는 방법에 대해 알아보았습니다.

JSON 데이터 타입을 활용하면 복잡한 데이터 구조를 유연하게 관리할 수 있다는 장점이 있습니다. JSON_EXTRACT 함수와 ->, ->> 연산자를 사용하면 원하는 데이터를 손쉽게 추출할 수 있죠.

 

이 글이 도움 되셨길 바랍니다!

---

참고 자료

• MySQL 공식 문서 - JSON 데이터 타입
• MySQL 공식 문서 - JSON 함수