MariaDB JSON 데이터 사용하기

admin

MariaDB JSON 형태의 데이터를 입력/수정/추가

MariaDB 10.2 부터 사용가능

1.  버젼 확인

MariaDB [(none)]> select @@version ;
+----------------+
| @@version |
+----------------+
| 10.4.7-MariaDB |
+----------------+
1 row in set (0.000 sec)

2. 테이블 생성

MariaDB [galgulee]> create table json_test (
-> id int(11) NOT NULL AUTO_INCREMENT,
-> data json ,
-> PRIMARY KEY (id)
-> ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected (0.005 sec)

MariaDB [galgulee]> desc json_test;
+-------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| data | longtext | YES | | NULL | |
+-------+----------+------+-----+---------+----------------+
2 rows in set (0.001 sec)

* 필드는 longtext 타입으로 생성된다

3. JSON 데이터 입력

MariaDB [galgulee]> insert into json_test set data = '{"Name": "Test", "Phone": "1234-4567"}';
Query OK, 1 row affected (0.000 sec)

MariaDB [galgulee]> select * from json_test;
+----+----------------------------------------+
| id | data |
+----+----------------------------------------+
| 1 | {"Name": "Test", "Phone": "1234-4567"} |
+----+----------------------------------------+
1 row in set (0.000 sec)

MariaDB [galgulee]> insert into json_test set data = json_object('Name' , 'YoungSun Shin' , 'Sex' , 'M' , 'Phone' , '2922-0871');
Query OK, 1 row affected (0.000 sec)

MariaDB [galgulee]> select * from json_test;
+----+-------------------------------------------------------------+
| id | data |
+----+-------------------------------------------------------------+
| 1 | {"Name": "Test", "Phone": "1234-4567"} |
| 2 | {"Name": "YoungSun Shin", "Sex": "M", "Phone": "2922-0871"} |
+----+-------------------------------------------------------------+
2 rows in set (0.000 sec)

MariaDB [galgulee]>
MariaDB [galgulee]> insert into json_test set data = json_object('Name' , 'YoungSun2 Shin' , 'Sex' , 'M' , 'Phone' , '2922-0871', 'Email', '[email protected]');
Query OK, 1 row affected (0.000 sec)

MariaDB [galgulee]> select * from json_test;
+----+---------------------------------------------------------------------------------------------+
| id | data |
+----+---------------------------------------------------------------------------------------------+
| 1 | {"Name": "Test", "Phone": "1234-4567"} |
| 2 | {"Name": "YoungSun Shin", "Sex": "M", "Phone": "2922-0871"} |
| 3 | {"Name": "YoungSun2 Shin", "Sex": "M", "Phone": "2922-0871", "Email": "[email protected]"} |
+----+---------------------------------------------------------------------------------------------+
3 rows in set (0.000 sec)

MariaDB [galgulee]>
MariaDB [galgulee]> insert into json_test set data = json_object('Name', 'blackbox', 'Email', json_object('Private', '[email protected]', 'Work', '[email protected]'));
Query OK, 1 row affected (0.000 sec)

MariaDB [galgulee]> select * from json_test;
+----+---------------------------------------------------------------------------------------------------+
| id | data |
+----+---------------------------------------------------------------------------------------------------+
| 1 | {"Name": "Test", "Phone": "1234-4567"} |
| 2 | {"Name": "YoungSun Shin", "Sex": "M", "Phone": "2922-0871"} |
| 3 | {"Name": "YoungSun2 Shin", "Sex": "M", "Phone": "2922-0871", "Email": "[email protected]"} |
| 4 | {"Name": "blackbox", "Email": {"Private": "[email protected]", "Work": "[email protected]"}} |
+----+---------------------------------------------------------------------------------------------------+
4 rows in set (0.000 sec)

MariaDB [galgulee]>
MariaDB [galgulee]> insert into json_test set data = json_object('Name', 'galgulee', 'Email', json_object('Private', '[email protected]', 'Work', '[email protected]'));
Query OK, 1 row affected (0.000 sec)

MariaDB [galgulee]> select * from json_test;
+----+---------------------------------------------------------------------------------------------------+
| id | data |
+----+---------------------------------------------------------------------------------------------------+
| 1 | {"Name": "Test", "Phone": "1234-4567"} |
| 2 | {"Name": "YoungSun Shin", "Sex": "M", "Phone": "2922-0871"} |
| 3 | {"Name": "YoungSun2 Shin", "Sex": "M", "Phone": "2922-0871", "Email": "[email protected]"} |
| 4 | {"Name": "blackbox", "Email": {"Private": "[email protected]", "Work": "[email protected]"}} |
| 5 | {"Name": "galgulee", "Email": {"Private": "[email protected]", "Work": "[email protected]"}} |
+----+---------------------------------------------------------------------------------------------------+
5 rows in set (0.000 sec)

MariaDB [galgulee]>
MariaDB [galgulee]> insert into json_test set data = json_object('Name', '갈머리', 'Email', json_object('private', '[email protected]'), 'Social', json_object('instagram', '@galgulee', 'homepage', 'https://www.galgulee.com'));
Query OK, 1 row affected (0.000 sec)

MariaDB [galgulee]> select * from json_test;
+----+-------------------------------------------------------------------------------------------------------------------------------------------------+
| id | data |
+----+-------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | {"Name": "Test", "Phone": "1234-4567"} |
| 2 | {"Name": "YoungSun Shin", "Sex": "M", "Phone": "2922-0871"} |
| 3 | {"Name": "YoungSun2 Shin", "Sex": "M", "Phone": "2922-0871", "Email": "[email protected]"} |
| 4 | {"Name": "blackbox", "Email": {"Private": "[email protected]", "Work": "[email protected]"}} |
| 5 | {"Name": "galgulee", "Email": {"Private": "[email protected]", "Work": "[email protected]"}} |
| 6 | {"Name": "갈머리", "Email": {"private": "[email protected]"}, "Social": {"instagram": "@galgulee", "homepage": "https://www.galgulee.com"}} |
+----+-------------------------------------------------------------------------------------------------------------------------------------------------+
6 rows in set (0.000 sec)

4.  특정 key 값만 조회 하고자 할때 – json_value

MariaDB [galgulee]> select id , json_value(data,'$.Name') As Name , json_value(data,'$.Phone') as Phone from json_test ;
+----+----------------+-----------+
| id | Name | Phone |
+----+----------------+-----------+
| 1 | Test | 1234-4567 |
| 2 | YoungSun Shin | 2922-0871 |
| 3 | YoungSun2 Shin | 2922-0871 |
| 4 | blackbox | NULL |
| 5 | galgulee | NULL |
| 6 | 갈머리 | NULL |
+----+----------------+-----------+
6 rows in set (0.000 sec)

MariaDB [galgulee]> select id , json_value(data,'$.Name') As Name , json_value(data,'$.Phone') , json_value(data,'$.Email') as Email from json_test ;
+----+----------------+----------------------------+--------------------+
| id | Name | json_value(data,'$.Phone') | Email |
+----+----------------+----------------------------+--------------------+
| 1 | Test | 1234-4567 | NULL |
| 2 | YoungSun Shin | 2922-0871 | NULL |
| 3 | YoungSun2 Shin | 2922-0871 | [email protected] |
| 4 | blackbox | NULL | NULL |
| 5 | galgulee | NULL | NULL |
| 6 | 갈머리 | NULL | NULL |
+----+----------------+----------------------------+--------------------+
6 rows in set (0.000 sec)

MariaDB [galgulee]> select id , json_value(data,'$.Name') As Name , json_value(data,'$.Phone') , json_value(data,'$.Email') as Email , json_value(data,'$.Social') as Social from json_test ;
+----+----------------+----------------------------+--------------------+--------+
| id | Name | json_value(data,'$.Phone') | Email | Social |
+----+----------------+----------------------------+--------------------+--------+
| 1 | Test | 1234-4567 | NULL | NULL |
| 2 | YoungSun Shin | 2922-0871 | NULL | NULL |
| 3 | YoungSun2 Shin | 2922-0871 | [email protected] | NULL |
| 4 | blackbox | NULL | NULL | NULL |
| 5 | galgulee | NULL | NULL | NULL |
| 6 | 갈머리 | NULL | NULL | NULL |
+----+----------------+----------------------------+--------------------+--------+
6 rows in set (0.000 sec)

MariaDB [galgulee]> select id , json_value(data,'$.Name') As Name , json_value(data,'$.Phone') , json_value(data,'$.Email.private') as PrivateEmail , json_value(data,'$.Social') as Social from json_test ;
+----+----------------+----------------------------+--------------------+--------+
| id | Name | json_value(data,'$.Phone') | PrivateEmail | Social |
+----+----------------+----------------------------+--------------------+--------+
| 1 | Test | 1234-4567 | NULL | NULL |
| 2 | YoungSun Shin | 2922-0871 | NULL | NULL |
| 3 | YoungSun2 Shin | 2922-0871 | NULL | NULL |
| 4 | blackbox | NULL | NULL | NULL |
| 5 | galgulee | NULL | NULL | NULL |
| 6 | 갈머리 | NULL | [email protected] | NULL |
+----+----------------+----------------------------+--------------------+--------+
6 rows in set (0.000 sec)

MariaDB [galgulee]> select id , json_value(data,'$.Name') As Name , json_value(data,'$.Phone') , json_value(data,'$.Email.private') as PrivateEmail , json_value(data,'$.Email.Work') as WorkEmail , json_value(data,'$.Social.instagram') as Instagram from json_test ;
+----+----------------+----------------------------+--------------------+-----------------------+-----------+
| id | Name | json_value(data,'$.Phone') | PrivateEmail | WorkEmail | Instagram |
+----+----------------+----------------------------+--------------------+-----------------------+-----------+
| 1 | Test | 1234-4567 | NULL | NULL | NULL |
| 2 | YoungSun Shin | 2922-0871 | NULL | NULL | NULL |
| 3 | YoungSun2 Shin | 2922-0871 | NULL | NULL | NULL |
| 4 | blackbox | NULL | NULL | [email protected] | NULL |
| 5 | galgulee | NULL | NULL | [email protected] | NULL |
| 6 | 갈머리 | NULL | [email protected] | NULL | @galgulee |
+----+----------------+----------------------------+--------------------+-----------------------+-----------+
6 rows in set (0.000 sec)

* 위 실행결과 화면

5. 특정 Key 값을 update 하고자 할때 – json_replace

MariaDB [galgulee]> update json_test set data = json_replace(data,'$.Name','Test1234') where id = 1 ;
Query OK, 1 row affected (0.000 sec)
Rows matched: 1 Changed: 1 Warnings: 0

MariaDB [galgulee]> select id , json_value(data,'$.Name') As Name , json_value(data,'$.Phone') , json_value(data,'$.Email.private') as PrivateEmail , json_value(data,'$.Email.Work') as WorkEmail , json_value(data,'$.Social.instagram') as Instagram from json_test ;
+----+----------------+----------------------------+--------------------+-----------------------+-----------+
| id | Name | json_value(data,'$.Phone') | PrivateEmail | WorkEmail | Instagram |
+----+----------------+----------------------------+--------------------+-----------------------+-----------+
| 1 | Test1234 | 7890-01234 | NULL | NULL | NULL |
| 2 | YoungSun Shin | 2922-0871 | NULL | NULL | NULL |
| 3 | YoungSun2 Shin | 2922-0871 | NULL | NULL | NULL |
| 4 | blackbox | NULL | NULL | [email protected] | NULL |
| 5 | galgulee | NULL | NULL | [email protected] | NULL |
| 6 | 갈머리 | NULL | [email protected] | NULL | @galgulee |
+----+----------------+----------------------------+--------------------+-----------------------+-----------+
6 rows in set (0.000 sec)




6. 하나 이상의 key값을 변경하고자 할때 또는 기존에 없는 key 항목을 추가 할때 - json_set

MariaDB [galgulee]> update json_test set data = json_set(data,'$.Phone','1111-2222') where id = 4 ;
Query OK, 1 row affected (0.000 sec)
Rows matched: 1 Changed: 1 Warnings: 0

MariaDB [galgulee]> select id , json_value(data,'$.Name') As Name , json_value(data,'$.Phone') , json_value(data,'$.Email.private') as PrivateEmail , json_value(data,'$.Email.Work') as WorkEmail , json_value(data,'$.Social.instagram') as Instagram from json_test ;
+----+----------------+----------------------------+--------------------+-----------------------+-----------+
| id | Name | json_value(data,'$.Phone') | PrivateEmail | WorkEmail | Instagram |
+----+----------------+----------------------------+--------------------+-----------------------+-----------+
| 1 | Sana | 7890-01234 | NULL | NULL | NULL |
| 2 | YoungSun Shin | 2922-0871 | NULL | NULL | NULL |
| 3 | YoungSun2 Shin | 2922-0871 | NULL | NULL | NULL |
| 4 | blackbox | 1111-2222 | NULL | [email protected] | NULL |
| 5 | galgulee | NULL | NULL | [email protected] | NULL |
| 6 | 갈머리 | NULL | [email protected] | NULL | @galgulee |
+----+----------------+----------------------------+--------------------+-----------------------+-----------+
6 rows in set (0.000 sec)

6. json 데이터 중 일부 데이터 조회

MariaDB [galgulee]> select id , json_value(data,'$.Name') As Name , json_value(data,'$.Phone') As Phone , json_value(data,'$.Email.private') as PrivateEmail , json_value(data,'$.Email.Work') as WorkEmail , json_value(data,'$.Social.instagram') as Instagram from json_test
-> where json_value(data,'$.Phone') like '%2222' ;
+----+----------+-----------+--------------+-----------------------+-----------+
| id | Name | Phone | PrivateEmail | WorkEmail | Instagram |
+----+----------+-----------+--------------+-----------------------+-----------+
| 4 | blackbox | 1111-2222 | NULL | [email protected] | NULL |
+----+----------+-----------+--------------+-----------------------+-----------+
1 row in set (0.000 sec)

MariaDB [galgulee]> explain select count(*) from json_test where json_value(data,'$.Phone') like '%2222' ;
+------+-------------+-----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-----------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | json_test | ALL | NULL | NULL | NULL | NULL | 6 | Using where |
+------+-------------+-----------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.003 sec)

7. json의 일부 key값을 이용하는 가상 칼럼을 생성 후 Index 작업 가능

MariaDB [galgulee]> alter table json_test add Phone varchar(20) as (json_value(data,'$.Phone'));
Query OK, 6 rows affected (0.003 sec)
Records: 6 Duplicates: 0 Warnings: 0

MariaDB [galgulee]> alter table json_test add index (Phone(20));
Query OK, 6 rows affected (0.002 sec)
Records: 6 Duplicates: 0 Warnings: 0

MariaDB [galgulee]> desc json_test;
+-------+-------------+------+-----+---------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| data | longtext | YES | | NULL | |
| Phone | varchar(20) | YES | MUL | NULL | VIRTUAL GENERATED |
+-------+-------------+------+-----+---------+-------------------+
3 rows in set (0.001 sec)

MariaDB [galgulee]> select * from json_test where Phone like '%2222';
+----+-------------------------------------------------------------------------------------------------------------------------+-----------+
| id | data | Phone |
+----+-------------------------------------------------------------------------------------------------------------------------+-----------+
| 4 | {"Name": "blackbox", "Email": {"Private": "[email protected]", "Work": "[email protected]"}, "Phone": "1111-2222"} | 1111-2222 |
+----+-------------------------------------------------------------------------------------------------------------------------+-----------+
1 row in set (0.000 sec)

MariaDB [galgulee]> explain select count(*) from json_test where Phone like '%2222';
+------+-------------+-----------+-------+---------------+-------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-----------+-------+---------------+-------+---------+------+------+--------------------------+
| 1 | SIMPLE | json_test | index | NULL | Phone | 83 | NULL | 6 | Using where; Using index |
+------+-------------+-----------+-------+---------------+-------+---------+------+------+--------------------------+
1 row in set (0.000 sec)

답글 남기기

이메일 주소는 공개되지 않습니다. 필수 필드는 *로 표시됩니다