MariaDB JSON 형식 데이터 사용 : MariaDB 10.2 부터 가능
참고 사이트
https://mariadb.com/kb/en/library/json-functions/
https://mariadb.com/resources/blog/json-with-mariadb-10-2/
https://bstar36.tistory.com/359
1. 버전 확인
MariaDB [(test)]> select @@version;
+-----------------+
| @@version |
+-----------------+
| 10.3.17-MariaDB |
+-----------------+
1 row in set (0.000 sec)
2. JSON 데이터 타입 지원(내부적으로 LongTEXT로 저장)
MariaDB [test]> create table json_test (id int, data json);
Query OK, 0 rows affected (0.015 sec)
MariaDB [test]> show create table json_test;
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| json_test | CREATE TABLE `json_test` (
`id` int(11) DEFAULT NULL,
`data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)
3. JSON 데이터 조작
A. json_object 함수를 이용하여 key,value 형식으로 Insert
MariaDB [test]> insert into json_test values (1 , json_object('Name' , 'Kil-Dong, Hong' , 'Sex' , 'M' , 'Phone' , '010-1234-5678')) ;
Query OK, 1 row affected (0.003 sec)
MariaDB [test]> commit;
Query OK, 0 rows affected (0.000 sec)
MariaDB [test]> select * from json_test ;
+------+------------------------------------------------------------------+
| id | data |
+------+------------------------------------------------------------------+
| 1 | {"Name": "Kil-Dong, Hong", "Sex": "M", "Phone": "010-1234-5678"} |
+------+------------------------------------------------------------------+
1 row in set (0.000 sec)
B. 특정 key 값만 조회 하고자 할때 json_value 함수를 사용
MariaDB [test]> select id , json_value(data,'$.Name') As Name , json_value(data,'$.Phone') as Phone from json_test ;
+------+----------------+---------------+
| id | Name | Phone |
+------+----------------+---------------+
| 1 | Kil-Dong, Hong | 010-1234-5678 |
+------+----------------+---------------+
1 row in set (0.001 sec)
C. 특정 하나 Key 값을 update 하고자 할때, json_replace 함수를 사용
※ 단, 이름은 중복날 수 있으니 Unique 값으로 비교하는 것을 추천 (사용방법 안내)
MariaDB [test]> update json_test set data = json_replace(data,'$.Phone','010-2345-6789') where json_value(data,'$.Name') = 'Kil-Dong, Hong';
Query OK, 1 row affected (0.002 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [test]> select id , json_value(data,'$.Name') As Name , json_value(data,'$.Phone') as Phone from json_test ;
+------+----------------+---------------+
| id | Name | Phone |
+------+----------------+---------------+
| 1 | Kil-Dong, Hong | 010-2345-6789 |
+------+----------------+---------------+
1 row in set (0.000 sec)
Object 값 자체로 추출("" 형태)
MariaDB [test]> SELECT JSON_EXTRACT(data, '$.Name') from json_test;
+------------------------------+
| JSON_EXTRACT(data, '$.Name') |
+------------------------------+
| "Su, Peng" |
| "Dori Go" |
| "DaHan, Wi " |
| NULL |
| NULL |
+------------------------------+
5 rows in set (0.000 sec)
MariaDB [test]> SELECT JSON_UNQUOTE(JSON_EXTRACT(data, '$.Name')) from json_test;
+--------------------------------------------+
| JSON_UNQUOTE(JSON_EXTRACT(data, '$.Name')) |
+--------------------------------------------+
| Su, Peng |
| Dori Go |
| DaHan, Wi |
| NULL |
| NULL |
+--------------------------------------------+
5 rows in set (0.000 sec)
D. 하나 이상의 Key 값을 변경할 때는 json_set 함수 사용
MariaDB [test]> update json_test set data = json_set(data,'$.Phone','010-3456-7890', '$.Name','Su, Peng') where id = 1 ;
Query OK, 1 row affected (0.001 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [test]> select id , json_value(data,'$.Name') As Name , json_value(data,'$.Phone') as Phone from json_test ;
+------+----------+---------------+
| id | Name | Phone |
+------+----------+---------------+
| 1 | Su, Peng | 010-3456-7890 |
+------+----------+---------------+
1 row in set (0.000 sec)
E. Json 형태가 추가되어도 입력이 가능
insert into json_test values (2 , json_object('Name' , 'Dori Go' , 'Sex' , 'F' , 'Phone' , '02-123-4567' , 'Birth', '2000-01-01')) ;
MariaDB [test]> insert into json_test values (2 , json_object('Name' , 'Dori Go' , 'Sex' , 'F' , 'Phone' , '02-123-4567' , 'Birth', '2000-01-01')) ;
Query OK, 1 row affected (0.001 sec)
MariaDB [test]> commit;
Query OK, 0 rows affected (0.000 sec)
MariaDB [test]> select * from json_test;
+------+--------------------------------------------------------------------------------+
| id | data |
+------+--------------------------------------------------------------------------------+
| 1 | {"Name": "Su, Peng", "Sex": "M", "Phone": "010-3456-7890"} |
| 2 | {"Name": "Dori Go", "Sex": "F", "Phone": "02-123-4567", "Birth": "2000-01-01"} |
+------+--------------------------------------------------------------------------------+
2 rows in set (0.000 sec)
F. json 데이터 값의 Like 검색
MariaDB [test]> select count(*) from json_test where json_value(data,'$.Phone') like '02%' ;
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row in set (0.000 sec)
G. json 데이터 타입의 key를 가상 컬럼을 생성 후 Index 작업 가능
- 테이블 변경(가상 컬럼 추가)
MariaDB [test]> alter table json_test add phone varchar(20) as (json_value(data,'$.phone')) ;
Query OK, 0 rows affected (0.341 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [test]> select * from json_test limit 1 ;
+------+------------------------------------------------------------+-------+
| id | data | phone |
+------+------------------------------------------------------------+-------+
| 1 | {"Name": "Su, Peng", "Sex": "M", "Phone": "010-3456-7890"} | NULL |
+------+------------------------------------------------------------+-------+
1 row in set (0.000 sec)
현재 화면에서 데이터가 안나오는 이유는 Key의 Phone은 P가 대문자 생성시에는 소문자로 만들었음
삭제 후 다시 추가
MariaDB [test]> alter table json_test drop phone;
Query OK, 0 rows affected (0.009 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [test]> select * from json_test limit 1 ;
+------+------------------------------------------------------------+
| id | data |
+------+------------------------------------------------------------+
| 1 | {"Name": "Su, Peng", "Sex": "M", "Phone": "010-3456-7890"} |
+------+------------------------------------------------------------+
1 row in set (0.000 sec)
MariaDB [test]> alter table json_test add phone varchar(20) as (json_value(data,'$.Phone')) ;
Query OK, 0 rows affected (0.012 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [test]> select * from json_test;
+------+--------------------------------------------------------------------------------+---------------+
| id | data | phone |
+------+--------------------------------------------------------------------------------+---------------+
| 1 | {"Name": "Su, Peng", "Sex": "M", "Phone": "010-3456-7890"} | 010-3456-7890 |
| 2 | {"Name": "Dori Go", "Sex": "F", "Phone": "02-123-4567", "Birth": "2000-01-01"} | 02-123-4567 |
+------+--------------------------------------------------------------------------------+---------------+
2 rows in set (0.000 sec)
- 인덱스 생성
key 값을 기준으로 가상 생성된 컬럼에 index를(ix_json_test_01) 추가
MariaDB [test]> create index ix_json_test_01 on json_test(phone) ;
Query OK, 0 rows affected (0.021 sec)
Records: 0 Duplicates: 0 Warnings: 0
explain 을 통해 plan을 조회합니다.
MariaDB [test]> explain select count(*) from json_test where Phone like '02%' ;
+------+-------------+-----------+-------+-----------------+-----------------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-----------+-------+-----------------+-----------------+---------+------+------+--------------------------+
| 1 | SIMPLE | json_test | index | ix_json_test_01 | ix_json_test_01 | 63 | NULL | 2 | Using where; Using index |
+------+-------------+-----------+-------+-----------------+-----------------+---------+------+------+--------------------------+
1 row in set (0.000 sec)
H. Json Object를 사용하지 않은 Insert
MariaDB [test]> insert into json_test(id, data) values (3, '{"Name": "DaHan, Wi ", "Sex": "M", "Phone": "010-9876-5432", "Birth": "1999-12-31"}');
Query OK, 1 row affected (0.003 sec)
MariaDB [test]> select * from json_test;
+------+-------------------------------------------------------------------------------------+---------------+
| id | data | phone |
+------+-------------------------------------------------------------------------------------+---------------+
| 1 | {"Name": "Su, Peng", "Sex": "M", "Phone": "010-3456-7890"} | 010-3456-7890 |
| 2 | {"Name": "Dori Go", "Sex": "F", "Phone": "02-123-4567", "Birth": "2000-01-01"} | 02-123-4567 |
| 3 | {"Name": "DaHan, Wi ", "Sex": "M", "Phone": "010-9876-5432", "Birth": "1999-12-31"} | 010-9876-5432 |
+------+-------------------------------------------------------------------------------------+---------------+
3 rows in set (0.000 sec)
4. Json 관련 함수들
관련 링크 : https://mariadb.com/kb/en/library/json-functions/
- JSON_Query와 JSON_VALUE 차이
아래와 같이 임시로 json 변수를 선언하고 json 데이터를 저장합니다.
MariaDB [test]> SET @json='{ "x": [0,1], "y": "[0,1]", "z": "Monty" }';
Query OK, 0 rows affected (0.001 sec)
MariaDB [test]> SELECT JSON_QUERY(@json,'$'), JSON_VALUE(@json,'$');
+--------------------------------------------+-----------------------+
| JSON_QUERY(@json,'$') | JSON_VALUE(@json,'$') |
+--------------------------------------------+-----------------------+
| { "x": [0,1], "y": "[0,1]", "z": "Monty" } | NULL |
+--------------------------------------------+-----------------------+
1 row in set (0.000 sec)
MariaDB [test]> SELECT JSON_QUERY(@json,'$.x'), JSON_VALUE(@json,'$.x');
+-------------------------+-------------------------+
| JSON_QUERY(@json,'$.x') | JSON_VALUE(@json,'$.x') |
+-------------------------+-------------------------+
| [0,1] | NULL |
+-------------------------+-------------------------+
1 row in set (0.000 sec)
MariaDB [test]> SELECT JSON_QUERY(@json,'$.y'), JSON_VALUE(@json,'$.y');
+-------------------------+-------------------------+
| JSON_QUERY(@json,'$.y') | JSON_VALUE(@json,'$.y') |
+-------------------------+-------------------------+
| NULL | [0,1] |
+-------------------------+-------------------------+
1 row in set (0.000 sec)
MariaDB [test]> SELECT JSON_QUERY(@json,'$.z'), JSON_VALUE(@json,'$.z');
+-------------------------+-------------------------+
| JSON_QUERY(@json,'$.z') | JSON_VALUE(@json,'$.z') |
+-------------------------+-------------------------+
| NULL | Monty |
+-------------------------+-------------------------+
1 row in set (0.000 sec)
MariaDB [test]> SELECT JSON_QUERY(@json,'$.x[0]'), JSON_VALUE(@json,'$.x[0]');
+----------------------------+----------------------------+
| JSON_QUERY(@json,'$.x[0]') | JSON_VALUE(@json,'$.x[0]') |
+----------------------------+----------------------------+
| NULL | 0 |
+----------------------------+----------------------------+
1 row in set (0.000 sec)
- JSON_ARRAY 함수
10.2.3 버전부터 추가됨
MariaDB [test]> SELECT Json_Array(56, 3.1416, 'My name is "Foo"', NULL);
+--------------------------------------------------+
| Json_Array(56, 3.1416, 'My name is "Foo"', NULL) |
+--------------------------------------------------+
| [56, 3.1416, "My name is \"Foo\"", null] |
+--------------------------------------------------+
1 row in set (0.000 sec)
MariaDB [test]> insert into json_test(id, data) values (4, JSON_ARRAY("Name","Ra, Ro ", "Sex", "F", "Phone", "010-1122-3344", "Birth", "1990-06-01"));
Query OK, 1 row affected (0.003 sec)
MariaDB [test]> select * from json_test;
+------+-------------------------------------------------------------------------------------+---------------+
| id | data | phone |
+------+-------------------------------------------------------------------------------------+---------------+
| 1 | {"Name": "Su, Peng", "Sex": "M", "Phone": "010-3456-7890"} | 010-3456-7890 |
| 2 | {"Name": "Dori Go", "Sex": "F", "Phone": "02-123-4567", "Birth": "2000-01-01"} | 02-123-4567 |
| 3 | {"Name": "DaHan, Wi ", "Sex": "M", "Phone": "010-9876-5432", "Birth": "1999-12-31"} | 010-9876-5432 |
| 4 | ["Name", "Ra, Ro ", "Sex", "F", "Phone", "010-1122-3344", "Birth", "1990-06-01"] | NULL |
+------+-------------------------------------------------------------------------------------+---------------+
4 rows in set (0.000 sec)
위의 id 4번 값은 아래에 보듯이 Type이 맞지 않기 때문에 phone값이 정상 인식 되지 않았음.
MariaDB [test]> select json_type(data) from json_test;
+-----------------+
| json_type(data) |
+-----------------+
| OBJECT |
| OBJECT |
| OBJECT |
| ARRAY |
+-----------------+
4 rows in set (0.001 sec)
- 함수들 간략한 설명
2
JSON_QUERY와 JSON_VALUE의 차이점
JSON_QUERY와 JSON_VALUE의 예제와 비교합니다.
JSON_ARRAY
나열된 값이 포함 된 JSON 배열을 반환합니다.
JSON_ARRAY_APPEND
JSON 문서 내에서 주어진 배열의 끝에 값을 추가합니다.
JSON_ARRAY_INSERT
JSON 문서에 값을 삽입합니다.
JSON_COMPACT
불필요한 모든 공간을 제거하여 json 문서가 가능한 한 짧습니다.
JSON_CONTAINS
지정된 JSON 문서 또는 문서 내의 지정된 경로에서 값을 찾을 수 있는지 여부
JSON_CONTAINS_PATH
지정된 JSON 문서에 지정된 경로의 데이터가 있는지 여부를 나타냅니다.
JSON_DEPTH
JSON 문서의 최대 깊이.
JSON_DETAILED
중첩 구조를 강조하는 가장 이해하기 쉬운 방식으로 JSON을 나타냅니다.
JSON_EXISTS
지정된 데이터에 지정된 JSON 값이 있는지 확인합니다.
JSON_EXTRACT
JSON 문서에서 데이터를 추출합니다.
JSON_INSERT
JSON 문서에 데이터를 삽입합니다.
JSON_KEYS
JSON 객체의 최상위 값에서 키를 반환하거나 경로에서 최상위 키를 반환합니다.
MariaDB [test]> select id, json_keys(data) from json_test;
+------+-----------------------------------+
| id | json_keys(data) |
+------+-----------------------------------+
| 1 | ["Name", "Sex", "Phone"] |
| 2 | ["Name", "Sex", "Phone", "Birth"] |
| 3 | ["Name", "Sex", "Phone", "Birth"] |
| 4 | NULL |
| 5 | NULL |
+------+-----------------------------------+
5 rows in set (0.000 sec)
JSON_LENGTH
JSON 문서의 길이 또는 문서 내 값의 길이를 반환합니다.
JSON_LOOSE
더 읽기 쉽게 보이도록 JSON 문서에 공백을 추가합니다.
JSON_MERGE
주어진 JSON 문서를 병합합니다.
JSON_MERGE_PATCH
주어진 JSON 문서의 RFC 7396 호환 병합
JSON_MERGE_PRESERVE
JSON_MERGE의 동의어
JSON_OBJECT
주어진 키 / 값 쌍을 포함하는 JSON 객체를 반환합니다.
JSON_QUERY
JSON 문서가 주어지면 경로로 지정된 객체 또는 배열을 반환합니다.
JSON_QUOTE
문자열을 JSON 값으로 인용합니다.
JSON_REMOVE
JSON 문서에서 데이터를 제거합니다.
JSON_REPLACE
JSON 문서에서 기존 값을 바꿉니다.
JSON_SEARCH
JSON 문서 내에서 지정된 문자열의 경로를 반환합니다.
JSON_SET
JSON 문서에 데이터를 업데이트하거나 삽입합니다.
JSON_TYPE
JSON 값의 유형을 반환합니다.
JSON_UNQUOTE
JSON 값을 인용 해제하여 문자열을 반환합니다.
JSON_VALID
값이 유효한 JSON 문서인지 여부
JSON_VALUE
JSON 문서가 주어지면 지정된 스칼라를 반환합니다.
