✍️ 서론
MySQL 5.7부터 JSON 타입을 지원하면서 JSON 타입을 다루기 위한 여러 함수들이 추가되었다. 관련 함수의 사용법을 예제와 함께 정리해 보려 한다.
🍊 테이블 + 데이터 생성
예제 테이블 생성
CREATE TABLE user (
id INT(11) NOT NULL,
info JSON NULL,
PRIMARY KEY (id))
ENGINE = InnoDB;예제 데이터 생성
INSERT user
VALUES(1, '{"name": "kangworld", "age": 20 "address": "seoul", "hobby": ["baseball", "bowling"]}');mysql> SELECT * FROM user;
+----+----------------------------------------------------------------------------------------+
| id | info |
+----+----------------------------------------------------------------------------------------+
| 1 | {"age": 20, "name": "kangworld", "hobby": ["baseball", "bowling"], "address": "seoul"} |
+----+----------------------------------------------------------------------------------------+
🌱 JSON 함수 예제
1. JSON_OBJECT ([key, val[, key, val] ...])
JSON_OBJECT는 key-value 입력값을 JSON 객체로 반환한다.
mysql> SELECT JSON_OBJECT("name", "kangworld", "age", 10, "address", "seoul", "hobby", CAST('["baseball", "bowling"]' AS JSON));
+------------------------------------------------------------------------------------------------------------------+
| JSON_OBJECT("name", "kangworld", "age", 10, "address", "seoul", "hobby", CAST('["baseball", "bowling"]' AS JSON)) |
+------------------------------------------------------------------------------------------------------------------+
| {"age": 10, "name": "kangworld", "hobby": ["baseball", "bowling"], "address": "seoul"} |
+------------------------------------------------------------------------------------------------------------------+
응용해서 INSERT 쿼리에 활용 가능하다.
mysql> INSERT user
VALUES(2, JSON_OBJECT("name", "helloworld", "age", 15, "address", "LA", "hobby", CAST('["soccer", "bowling"]' AS JSON)));
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM user;
+----+----------------------------------------------------------------------------------------+
| id | info |
+----+----------------------------------------------------------------------------------------+
| 1 | {"age": 20, "name": "kangworld", "hobby": ["baseball", "bowling"], "address": "seoul"} |
| 2 | {"age": 15, "name": "helloworld", "hobby": ["soccer", "bowling"], "address": "LA"} |
+----+----------------------------------------------------------------------------------------+
2. JSON_ARRAY([val[, val] ...])
JSON_ARRAY는 입력을 JSON 배열로 반환한다.
mysql> SELECT JSON_ARRAY("baseball", "bowling", 10);
+---------------------------------------+
| JSON_ARRAY("baseball", "bowling", 10) |
+---------------------------------------+
| ["baseball", "bowling", 10] |
+---------------------------------------+3. column -> path (= JSON_EXTRACT)
연산자 ->로 JSON value에 접근할 수 있다.
mysql> SELECT id, info->'$.name', info->'$.age' FROM user;
+----+----------------+---------------+
| id | info->'$.name' | info->'$.age' |
+----+----------------+---------------+
| 1 | "kangworld" | 20 |
+----+----------------+---------------+
연산자 ->는 JSON_EXTRACT와 동일하다.
mysql> SELECT id, JSON_EXTRACT(info, '$.name'), JSON_EXTRACT(info, '$.age') FROM user;
+----+------------------------------+-----------------------------+
| id | JSON_EXTRACT(info, '$.name') | JSON_EXTRACT(info, '$.age') |
+----+------------------------------+-----------------------------+
| 1 | "kangworld" | 20 |
+----+------------------------------+-----------------------------+
value가 JSON 배열이라면 인덱스를 지정해야 한다.
# 두 쿼리 동일
mysql> SELECT info->'$.hobby[0]' FROM user;
mysql> SELECT JSON_EXTRACT(info, '$.hobby[0]') FROM user;
+----------------------------------+
| JSON_EXTRACT(info, '$.hobby[0]') |
+----------------------------------+
| "baseball" |
+----------------------------------+
* WHERE 절에 응용
mysql> SELECT * FROM user WHERE JSON_EXTRACT(info, '$.name') LIKE '%kang%';
+----+----------------------------------------------------------------------------------------+
| id | info |
+----+----------------------------------------------------------------------------------------+
| 1 | {"age": 20, "name": "kangworld", "hobby": ["baseball", "bowling"], "address": "seoul"} |
+----+----------------------------------------------------------------------------------------+
4. JSON_INSERT(json_doc, path, val[, path, val] ...)
JSON 데이터에 새로운 key-value를 추가하기 위해 사용된다. key가 없다면 입력되고 key가 있다면 입력은 무시된다.
# "$.gender", "W" 무시됨
mysql> SELECT JSON_INSERT(info, "$.gender", "M", "$.gender", "W") FROM user;
+-------------------------------------------------------------------------------------------------------+
| JSON_INSERT(info, "$.gender", "M", "$.gender", "W") |
+-------------------------------------------------------------------------------------------------------+
| {"age": 20, "name": "kangworld", "hobby": ["baseball", "bowling"], "gender": "M", "address": "seoul"} |
+-------------------------------------------------------------------------------------------------------+
5. JSON_REPLACE(json_doc, path, val[, path, val] ...)
JSON 데이터의 value를 수정하기 위해 사용된다. key가 있으면 수정되고 key가 없다면 수정은 무시된다.
mysql> SELECT JSON_REPLACE(info, "$.address", "gimhae", "$.abc", "abc") FROM user;
+-----------------------------------------------------------------------------------------+
| JSON_REPLACE(info, "$.address", "gimhae", "$.abc", "abc") |
+-----------------------------------------------------------------------------------------+
| {"age": 20, "name": "kangworld", "hobby": ["baseball", "bowling"], "address": "gimhae"} |
+-----------------------------------------------------------------------------------------+
6. JSON_SET(json_doc, path, val[, path, val] ...)
JSON 데이터의 입력 혹은 수정을 위해 사용된다. key가 없다면 key-value가 입력되고 key가 있다면 value가 수정된다.
#age 수정, gender 추가
mysql> SELECT JSON_SET(info, "$.age", 99, "$.gender", "M") FROM user;
+-------------------------------------------------------------------------------------------------------+
| JSON_SET(info, "$.age", 99, "$.gender", "M") |
+-------------------------------------------------------------------------------------------------------+
| {"age": 99, "name": "kangworld", "hobby": ["baseball", "bowling"], "gender": "M", "address": "seoul"} |
+-------------------------------------------------------------------------------------------------------+
7. JSON_QUOTE(string)
string을 큰따옴표로 감싸서 JSON value 형태로 변환한다. (동시에 내부의 큰따옴표와 특정 문자를 이스케이프 문자로 변환한다.)
mysql> SELECT JSON_QUOTE('hellow world');
+----------------------------+
| JSON_QUOTE('hellow world') |
+----------------------------+
| "hellow world" |
+----------------------------+
* JSON_CONTAINS는 문자열만 받는 반면 다양한 타입을 변환하고 싶다면 CAST(value AS JSON)을 사용하면 된다.
# integer
mysql> SELECT CAST(10 as JSON);
+------------------+
| CAST(10 as JSON) |
+------------------+
| 10 |
+------------------+
# array
mysql> SELECT CAST('[10,20]' as JSON);
+-------------------------+
| CAST('[10,20]' as JSON) |
+-------------------------+
| [10, 20] |
+-------------------------+
# string
mysql> SELECT CAST('"hello world"' as JSON);
+-------------------------------+
| CAST('"hello world"' as JSON) |
+-------------------------------+
| "hello world" |
+-------------------------------+
8. JSON_CONTAINS(target, candidate[, path])
JSON 데이터 candidate가 target 혹은 target path에 존재하는지 판단하기 위해 사용된다.
(1) JSON_CONTAINS(target, candidate) case
# path 없는 케이스
mysql> SELECT JSON_CONTAINS(info, JSON_OBJECT("age", 20)) FROM user;
+--------------------------------------------+
| JSON_CONTAINS(info, JSON_OBJECT("age",20)) |
+--------------------------------------------+
| 1 |
+--------------------------------------------+
(2) JSON_CONTAINS(target, candidate, [, path]) case
# path가 포함된 케이스
mysql> SELECT JSON_CONTAINS(info, JSON_QUOTE('kangworld'), '$.name') FROM user;
+--------------------------------------------------------+
| JSON_CONTAINS(info, JSON_QUOTE('kangworld'), '$.name') |
+--------------------------------------------------------+
| 1 |
+--------------------------------------------------------+