(꿀팁) Mysql에서 JSON 저장과 조회하기

최근 데이터의 형태가 복잡해 지면서 다양한 인터넷 컨텐츠 저장을 위해서 RDBMS, NoSQL, 검색엔진 등 다양한 기술들을 고려하여야 하는 상황들이 있습니다.
지금까지 데이터 관리를 위해서 데이터간의 관계를 정의하고 데이터 형식을 정규화하여 데이터를 저장하는데 관심을 가져 왔습니다.
이제는 기존의 데이터형태를 벗어나 검색이나 인공지능을 위해서 정형화 하기 힘든 데이터를 저장하고 관리해야 하는 상황들이 많이 생겨 났습니다.
우리는 이러한 상황에서 NoSQL (Mongo, Cassandra, Druid 등)의 다양한 Object저장소들을 활용하고 있습니다.

[데이터 베이스 환경의 변화]
기존의 RDBMS로는 세상의 모든 데이터를 정형화하여 관리할 수 없는 한계 환경에 다가왔다. 클라우드 시스템의 환경이 보편화 되면서 기존 서비스를 위해 필수적으로 사용했던 오라클, Mysql, MS-SQL등의 온프레미스 데이터베이스들도 클라우드 환경으로 전환하여 사용하는 환경으로 바뀐것도 데이터베이스 시스템의 변화라 할수 있습니다. 또한 최근 인공지능의 확대에 따라 데이터간의 복잡한 인과관계를 표현 할 수 있는 Graph DBMS도 활용 되고 있다. 이와 같이 데이터베이스는 다양한 데이터와 데이터간의 관계를 정의하여 지식을 관리하거나 복잡한 데이터의 알고리즘을 표현하는데 활용하고 있습니다.
이런 점에서 DBMS의 변화는 새로운 방향이 필요합니다. 대부분 데이터베이스 시스템들은 CLOB, BLOB, TEXT 등의 형태로 바이너리 데이터도 저장할 수 있습니다. 기존의 행,렬, 필드, 컬럼 단위의 데이터 관리 방법에서 Document 기반의 데이터 형식으로 변화하고있습니다.
기존에는 RDBMS에서 테이블과 행 렬 그리고 컬럼을 기반으로 정수 숫자 시간 텍스트를 넣는 정도로 사용하고 있었다면, RDBMS들은 이미 10년 전부터 ORDBMS라는 개념으로 기존의 정형 데이터 뿐아니라 Object를 관리할 수 있게 되었습니다. 이제는 Json이나 XML을 담고 처리하는데 기존 RDB를 활용 하는 것도 중요한 방향을 보여 집니다.

DB에서 Json 을 언제 사용하는 것이 좋을까?

- 빠른 조회보다 데이터의 저장에 중점을 두는 데이터
- 변화가 없으면서 정형화되지 않으면서 다양한 형식을 변화가 요구되는 데이터
- 어플리케이션을 통해 데이터를 처리하고 DB에는 단순한 저장이 요구되는 데이터
- 지속적인 데이터의 형식에 변화가 가능한 데이터

Mysql은 XML, JSON 등의 데이터를 저장 할 수도 있고, 검색을 할 수도 있게 되어 DB의 활용 측면에서 다양한 일들을 할수 있습니다. 예시를 바탕으로 JSON을 사용하는 방법을 소개합니다.

JSON Data 정의 하기

custinfo를 JSON 데이터 형식을 정의하고, zipcode에 인덱스를 정의하는 예제 입니다.

CREATE TABLE customers (
id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
custinfo JSON,
INDEX customer_zips( (CAST(custinfo->"$.zipcode" AS UNSIGNED ARRAY)) )
);

JSON을 이용한 데이터 정의와 활용

JSON 테이블 정의

mysql> CREATE TABLE customers (
id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
custinfo JSON
);
Query OK, 0 rows affected (0.51 sec)

JSON데이터 검색

custinfo (json) 데이터에서 zipcode를 추출합니다.

mysql>select JSON_EXTRACT( zipcode, '$.custinfo' ) from customers where id = 2;

JSON데이터 조건 조회 - MEMBER OF

custinfo (json) 데이터에서 zipcode가 94507이 포함된 데이터를 조회 합니다.

mysql> SELECT * FROM customers WHERE 94507 MEMBER OF( custinfo->'$.zipcode' );
+----+---------------------+-------------------------------------------------------------------+
| id | modified | custinfo |
+----+---------------------+-------------------------------------------------------------------+
| 2 | 2019-06-29 22:23:12 | {"user": "Jill", "user_id": 22, "zipcode": [94568, 94507, 94582]} |
| 3 | 2019-06-29 22:23:12 | {"user": "Bob", "user_id": 31, "zipcode": [94477, 94507]} |
| 5 | 2019-06-29 22:23:12 | {"user": "Ted", "user_id": 56, "zipcode": [94507, 94582]} |
+----+---------------------+-------------------------------------------------------------------+
3 rows in set (0.00 sec)

JSON데이터 조건 조회 - JSON_CONTAINS을 이용한 Array 조회

custinfo (json) 데이터에서 zipcode가 94507과 94582이 포함된 데이터를 조회 합니다.

mysql> SELECT * FROM customers WHERE JSON_CONTAINS(custinfo->'$.zipcode', CAST('[94507,94582]' AS JSON));
+----+---------------------+-------------------------------------------------------------------+
| id | modified | custinfo |
+----+---------------------+-------------------------------------------------------------------+
| 2 | 2019-06-29 22:23:12 | {"user": "Jill", "user_id": 22, "zipcode": [94568, 94507, 94582]} |
| 5 | 2019-06-29 22:23:12 | {"user": "Ted", "user_id": 56, "zipcode": [94507, 94582]}
| +----+---------------------+-------------------------------------------------------------------+
2 rows in set (0.00 sec)

JSON_CONTAINS(target, candidate[, path])

주어진 candidateJSON 문서가 JSON 문서에 포함되어 target있는지 또는 path 인수가 제공된 경우 대상 내의 특정 경로에서 후보가 발견 되는지 여부를 1 또는 0을 반환하여 나타냅니다

JSON데이터 조건 조회 - JSON_OVERLAPS을 이용한 Array 조회

custinfo (json) 데이터에서 zipcode가 94507이나 94582이 포함된 데이터를 조회 합니다.

mysql> SELECT * FROM customers WHERE JSON_OVERLAPS( custinfo->'$.zipcode', CAST('[94507,94582]' AS JSON));
+----+---------------------+-------------------------------------------------------------------+
| id | modified | custinfo |
+----+---------------------+-------------------------------------------------------------------+
| 1 | 2019-06-29 22:23:12 | {"user": "Jack", "user_id": 37, "zipcode": [94582, 94536]} |
| 2 | 2019-06-29 22:23:12 | {"user": "Jill", "user_id": 22, "zipcode": [94568, 94507, 94582]} |
| 3 | 2019-06-29 22:23:12 | {"user": "Bob", "user_id": 31, "zipcode": [94477, 94507]} |
| 5 | 2019-06-29 22:23:12 | {"user": "Ted", "user_id": 56, "zipcode": [94507, 94582]} |
+----+---------------------+-------------------------------------------------------------------+
4 rows in set (0.00 sec)

JSON_OVERLAPS(json_doc1, json_doc2)

두 개의 JSON 문서를 비교합니다. 두 문서에 공통된 키-값 쌍 또는 배열 요소가 있으면 true (1)를 반환합니다. 두 인수가 모두 스칼라이면 함수는 단순 동등성 테스트를 수행합니다.

JSON_EXTRACT(json_doc, path[, path] ...)

path 인수 와 일치하는 문서 부분에서 선택된 JSON 문서에서 데이터를 반환합니다 .

JSON_EXTRACT() 의 별칭 column->path 사용 비교

mysql> SELECT c, c->"$.id", g
FROM jemp
WHERE c->"$.id" > 1
ORDER BY c->"$.name";
+-------------------------------+-----------+------+
| c | c->"$.id" | g |
+-------------------------------+-----------+------+
| {"id": "3", "name": "Barney"} | "3" | 3 |
| {"id": "4", "name": "Betty"} | "4" | 4 |
| {"id": "2", "name": "Wilma"} | "2" | 2 |
+-------------------------------+-----------+------+
3 rows in set (0.00 sec)

JSON_EXTRACT()를 이용한 데이터 조회

mysql> SELECT c, JSON_EXTRACT(c, "$.id"), g
FROM jemp
WHERE JSON_EXTRACT(c, "$.id") > 1
ORDER BY JSON_EXTRACT(c, "$.name");
+-------------------------------+-----------+------+
| c | c->"$.id" | g |
+-------------------------------+-----------+------+
| {"id": "3", "name": "Barney"} | "3" | 3 |
| {"id": "4", "name": "Betty"} | "4" | 4 |
| {"id": "2", "name": "Wilma"} | "2" | 2 |
+-------------------------------+-----------+------+
3 rows in set (0.00 sec)

dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#operator_member-of

MySQL :: MySQL 8.0 Reference Manual :: 12.18.3 Functions That Search JSON Values