MySQL JSON 데이터 타입에 활용되는 함수 정리

MySQL JSON 함수

MySQL 5.7부터 DBMS 차원에서 JSON 데이터 타입이 지원되기 시작했는데요.

아래 내용은 최근 데이터베이스 쿼리에 JSON 함수를 사용해보면서 정리한 내용입니다.

 

 

- JSON 타입에 저장? TEXT 타입에 저장?

DB에 JSON 형식의 데이터를 저장할 때는 고려되어야 할 부분이 있는데요.

바로 해당 데이터를 저장할 컬럼의 타입으로 'JSON Type'을 쓸 것이냐, 'TEXT Type'을 쓸 것이냐, 입니다.

 

MySQL에서 JSON Type에 데이터를 넣는 경우 내부적으로 binary 형태로 저장되며, TEXT Type에 데이터를 넣는 경우에는 string 형태로 저장되는데요. TEXT 타입의 경우에는 Type Conversion 없이 string 형태의 데이터를 그대로 전송해주면 되지만, JSON 타입의 경우 binary 형태의 데이터가 Type Conversion을 거친 후 전송되기 때문에 속도가 TEXT 타입에 비해 느리다는 단점이 있습니다.

이처럼 속도 측면에서의 단점은 존재하지만 JSON 타입을 사용하는 경우 MySQL 서버가 JSON 데이터 구조를 인식하고 있다는 장점이 있는데요. 때문에 해당 컬럼에 저장된 데이터의 특정 필드를 추출하여 함수 기반의 인덱스를 생성하는 등의 기능을 사용할 수 있다고 합니다.

 

데이터베이스에 JSON 형식의 데이터를 저장하는 이유가 조회보다 데이터 저장에 중점을 두고, 정형화되지 않은 다양한 형식 변화가 요구되는 데이터를 저장하기 위함이라면 TEXT 타입이 많이 쓰일 수도 있겠다는 생각이 드는데요.

어디까지나 개인적인 생각일 뿐, 결론적으로는 JSON 데이터가 사용되는 상황에 맞는 데이터 타입을 써야 할 것 같습니다.

 

(해당 내용에 대한 당근마켓 팀블로그의 참고 자료를 포스팅 맨 하단에 첨부해 놓았습니다. 필요하시다면 함께 보셔도 좋을 것 같습니다.)

 

 

 

- MySQL JSON 함수 정리

user table

//idx = 1
INSERT INTO user (name, address, info) VALUES
('Kim', 'Seoul', '{"age":25, "hobby":"Painting", "hp":"01022222222"}');

//idx = 2
INSERT INTO user (name, address, info) VALUES
('Choi', 'Incheon', '{"hobbies":[{"name":"Cooking","canAlone":"y"},{"name":"Yoga","canAlone":"y"},{"name":"Chess","canAlone":"n"}]}');

(테스트에 사용된 테이블 및 데이터입니다. idx=1의 info 데이터와 idx=2의 info 데이터 형식이 다른 점 참고 부탁드립니다.)

 

 

1. JSON_OBJECT

mysql> SELECT JSON_OBJECT("name", name, "address", address) FROM user;
+-----------------------------------------------+
| JSON_OBJECT("name", name, "address", address) |
+-----------------------------------------------+
| {"name": "Kim", "address": "Seoul"}           |
| {"name": "Choi", "address": "Incheon"}        |
+-----------------------------------------------+

 

(SELECT)

mysql> INSERT INTO user (name, address, info)
    -> VALUES ('Lee', 'Sejong', JSON_OBJECT("age", 30, "hobby", "GYM", "hp", "010555555550"));

(INSERT)

 

JSON_OBJECT 함수는 key, value 쌍으로 입력되는 값들을 JSON 형식으로 만들어 반환하는데요. 위 예시와 같이 SELECT 또는 INSERT 등에서 사용할 수 있습니다.

(입력되는 key, value 쌍이 맞지 않는 경우는 오류가 발생합니다.)

 

 

2. JSON_ARRAY

mysql> SELECT JSON_ARRAY(name,address, info) FROM user;
+----------------------------------------------------------------------------------+
| JSON_ARRAY(name,address, info)                                                   |
+----------------------------------------------------------------------------------+
| ["Kim", "Seoul", "{\"age\":25, \"hobby\":\"Painting\", \"hp\":\"01022222222\"}"] |
| ["Lee", "Sejong", "{\"hp\": \"010555555550\", \"age\": 30, \"hobby\": \"GYM\"}"] |
+----------------------------------------------------------------------------------+

배열을 반환하는 JSON_ARRAY 함수입니다.

 

 

3. JSON_VALID

mysql> SELECT JSON_VALID(info) FROM user WHERE idx = 1;
+------------------+
| JSON_VALID(info) |
+------------------+
|                1 |
+------------------+

JSON_VALID는 해당 필드의 데이터 값이 JSON 형식이 맞는지 검사하는 함수입니다. JSON 형식이 맞다면 1(true)을 반환하고 아니라면 0(false)을 반환합니다.

 

 

4. JSON_EXTRACT

mysql> SELECT JSON_EXTRACT(info, '$.hobby') FROM user WHERE idx = 1;
+-------------------------------+
| JSON_EXTRACT(info, '$.hobby') |
+-------------------------------+
| "Painting"                    |
+-------------------------------+

mysql> SELECT JSON_EXTRACT(info, '$.hobbies[1].name') FROM user WHERE idx = 2;
+-----------------------------------------+
| JSON_EXTRACT(info, '$.hobbies[1].name') |
+-----------------------------------------+
| "Yoga"                                  |
+-----------------------------------------+

JSON_EXTRACT 함수를 사용하여 요청되는 경로에 해당하는 데이터를 반환받을 수 있습니다.

 

 

5. JSON_SET

UPDATE user SET info = JSON_SET(info, '$.hobby', 'Camping') WHERE idx = 1;
UPDATE user SET info = JSON_SET(info, '$.hobbies[0].insideActivity', 'y') WHERE idx = 2;

JSON_SET 함수의 경우에는 해당 JSON 데이터에 필드를 추가하거나 업데이트하기 위해서 사용되는데요. 같은 key를 가진 필드가 있을 경우 update 되고, 같은 key를 가진 필드가 없을 경우 insert 됩니다.

 

 

6. JSON_INSERT

UPDATE user SET info = JSON_INSERT(info, '$.zipcode', '54321') WHERE idx = 1;
UPDATE user SET info = JSON_INSERT(info, '$.hobbies[0].insideActivity', 'y') WHERE idx = 2;

JSON_INSERT 함수의 경우는 JSON 데이터에 새로운 필드를 추가하기 위해서만 사용됩니다. 이미 존재하는 key를 가진 필드에 대해서 요청되는 값은 update 되지 않습니다.

 

 

7. JSON_REPLACE

UPDATE user SET info = JSON_REPLACE(info, '$.zipcode', '11111') WHERE idx = 1;
UPDATE user SET info = JSON_REPLACE(info, '$.hobbies[0].insideActivity', 'n') WHERE idx = 2;

JSON_REPLACE 함수는 값의 변경에 사용되며, 존재하지 않는 key에 대한 필드의 요청은 무시됩니다.

 

 

8. JSON_REMOVE

UPDATE user SET info = JSON_REMOVE(info, '$.zipcode') WHERE idx = 1;
UPDATE user SET info = JSON_REMOVE(info, '$.hobbies[0].insideActivity') WHERE idx = 2;

JSON_REMOVE 함수는 요청되는 key를 가진 필드를 삭제하는 데 사용됩니다.

 

 

9. JSON_CONTAINS

mysql> SELECT JSON_CONTAINS(info, JSON_QUOTE('Camping'), '$.hobby') FROM user WHERE idx = 1;
+-------------------------------------------------------+
| JSON_CONTAINS(info, JSON_QUOTE('Camping'), '$.hobby') |
+-------------------------------------------------------+
|                                                     1 |
+-------------------------------------------------------+

mysql> SELECT JSON_CONTAINS(info, JSON_QUOTE('Yoga'), '$.hobbies[1].name') FROM user WHERE idx = 2;
+--------------------------------------------------------------+
| JSON_CONTAINS(info, JSON_QUOTE('Yoga'), '$.hobbies[1].name') |
+--------------------------------------------------------------+
|                                                            1 |
+--------------------------------------------------------------+

해당 데이터가 포함되어 있는지 확인하기 위해 사용되는 JSON_CONTAINS 함수입니다. 해당 데이터가 존재하면 1(true)을 아니면 0(false)을 반환합니다.

(확인하려는 값을 입력할 때 그냥 입력하면 오류가 발생하는데 JSON_QUOTE 함수를 사용하여 입력하면 정상적으로 동작합니다.)

 

 

 

 

< MySQL 공식 문서 >

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

 

 

< JSON 타입과 TEXT 타입 저장에 대한 당근마켓 팀블로그 포스팅 >

https://medium.com/daangn/json-vs-text-c2c1448b8b1f