이 랩에서는 MySQL 에서 JSON 데이터 타입을 효과적으로 사용하는 방법을 배웁니다. JSON 문서를 삽입하고, JSON_EXTRACT 함수 및 ->> 연산자를 사용하여 특정 필드를 쿼리하고, JSON 열 내의 데이터를 수정하고, JSON 속성에 인덱스를 생성하여 쿼리를 최적화하는 등의 기본적인 작업을 수행합니다.
이 랩을 진행하는 동안 MySQL 서버에 연결하고, 전용 데이터베이스와 테이블을 생성한 다음, 관계형 데이터베이스 컨텍스트 내에서 JSON 데이터를 관리하는 기술을 구축하기 위한 일련의 실습 작업을 수행합니다.
첫 번째 단계에서는 MySQL 서버에 연결하고 랩에 필요한 데이터베이스와 테이블을 설정합니다.
먼저 데스크톱에서 터미널을 엽니다.
root 사용자 권한으로 MySQL 서버에 연결합니다. 이 랩 환경에서는 sudo를 사용하면 비밀번호 없이 연결할 수 있습니다.
sudo mysql -u root연결되면 명령 프롬프트가 mysql>로 변경되어 MySQL 셸에 있음을 나타냅니다.
다음으로 jsondb라는 새 데이터베이스를 생성합니다. IF NOT EXISTS 절은 데이터베이스가 이미 존재하는 경우 오류 없이 명령을 실행하도록 합니다.
CREATE DATABASE IF NOT EXISTS jsondb;이제 새로 생성된 데이터베이스로 전환하여 후속 명령의 활성 데이터베이스로 만듭니다.
USE jsondb;마지막으로 products라는 테이블을 생성합니다. 이 테이블에는 상세한 제품 정보를 저장하기 위해 JSON 데이터 타입의 열이 포함됩니다.
CREATE TABLE IF NOT EXISTS products (
id INT AUTO_INCREMENT PRIMARY KEY,
product_name VARCHAR(255),
product_details JSON
);이 문은 세 개의 열을 가진 테이블을 정의합니다.
id: 각 레코드에 대한 고유한 자동 증가 정수입니다.product_name: 제품 이름에 대한 문자열입니다.product_details: 구조화된 데이터를 저장하는 JSON 열입니다.필요한 데이터베이스와 테이블 설정을 성공적으로 완료했습니다. 다음 단계를 위해 MySQL 셸을 열어 둡니다.
테이블을 생성했으므로 이제 JSON 문서를 포함하는 레코드를 삽입하고 이를 검색하기 위한 기본적인 쿼리를 수행합니다.
동일한 MySQL 셸에서 다음 INSERT 문을 실행하여 새 제품을 추가합니다.
INSERT INTO products (product_name, product_details) VALUES (
'Laptop',
'{
"brand": "Dell",
"model": "XPS 13",
"specs": {
"processor": "Intel Core i7",
"memory": "16GB",
"storage": "512GB SSD"
},
"price": 1200
}'
);이 명령은 'Laptop' 레코드를 삽입합니다. product_details 열에는 specs와 같은 중첩된 데이터를 포함하는 JSON 객체가 채워집니다.
데이터가 올바르게 삽입되었는지 확인하려면 products 테이블을 쿼리하여 내용을 확인합니다.
SELECT * FROM products;출력에는 방금 삽입한 행이 표시됩니다. product_details 열에 JSON 데이터가 어떻게 저장되는지 확인합니다.
+----+--------------+--------------------------------------------------------------------------------------------------------------------------------+
| id | product_name | product_details |
+----+--------------+--------------------------------------------------------------------------------------------------------------------------------+
| 1 | Laptop | {"brand": "Dell", "model": "XPS 13", "price": 1200, "specs": {"memory": "16GB", "storage": "512GB SSD", "processor": "Intel Core i7"}} |
+----+--------------+--------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)JSON 데이터가 포함된 레코드를 성공적으로 삽입했습니다. 다음 단계에서는 이 JSON 객체에서 특정 정보를 추출하는 방법을 배웁니다.
JSON 으로 데이터를 저장하는 것은 유용하지만, 그 안의 개별 필드를 쿼리할 수 있어야 합니다. 이 단계에서는 JSON_EXTRACT 함수와 JSON_UNQUOTE를 사용하여 product_details 열에서 특정 값을 가져옵니다.
JSON_EXTRACT 함수를 사용하면 경로 표현식 (path expression) 을 사용하여 JSON 문서에서 값을 선택할 수 있습니다. 경로는 문서 루트를 나타내기 위해 $로 시작합니다.
노트북의 brand를 추출해 보겠습니다.
SELECT JSON_EXTRACT(product_details, '$.brand') AS brand FROM products WHERE product_name = 'Laptop';이 쿼리는 브랜드 값을 반환하지만, 결과가 이중 따옴표를 포함하는 JSON 문자열임을 유의하십시오.
+--------+
| brand |
+--------+
| "Dell" |
+--------+
1 row in set (0.00 sec)더 깔끔한 결과를 얻으려면 JSON_EXTRACT와 JSON_UNQUOTE를 함께 사용할 수 있습니다. 이 조합은 값을 추출하고 따옴표를 제거하여 일반 문자열을 반환합니다.
SELECT JSON_UNQUOTE(JSON_EXTRACT(product_details, '$.brand')) AS brand FROM products WHERE product_name = 'Laptop';이제 출력은 일반 텍스트인 Dell입니다.
+-------+
| brand |
+-------+
| Dell |
+-------+
1 row in set (0.00 sec)경로 표현식을 사용하여 중첩된 객체의 값에 액세스할 수도 있습니다. specs 객체에서 processor를 가져오려면 경로 $.specs.processor를 사용합니다.
SELECT JSON_UNQUOTE(JSON_EXTRACT(product_details, '$.specs.processor')) AS processor FROM products WHERE product_name = 'Laptop';이렇게 하면 중첩된 값을 올바르게 추출할 수 있습니다.
+-----------------+
| processor |
+-----------------+
| Intel Core i7 |
+-----------------+
1 row in set (0.00 sec)이 함수들은 행을 필터링하기 위한 WHERE 절에서도 유용합니다. 1000 이 넘는 가격의 모든 제품을 찾으려면 비교를 위해 추출된 JSON 값을 숫자 타입으로 CAST해야 합니다.
SELECT product_name, JSON_UNQUOTE(JSON_EXTRACT(product_details, '$.price')) AS price FROM products WHERE CAST(JSON_UNQUOTE(JSON_EXTRACT(product_details, '$.price')) AS SIGNED) > 1000;이 쿼리는 JSON 필드 내의 숫자 값을 기반으로 레코드를 필터링하는 방법을 보여줍니다.
+--------------+-------+
| product_name | price |
+--------------+-------+
| Laptop | 1200 |
+--------------+-------+
1 row in set (0.00 sec)이제 JSON 필드를 기반으로 데이터를 추출하고 필터링하는 방법을 알게 되었습니다.
데이터는 시간이 지남에 따라 변경되므로 데이터베이스에 저장된 JSON 문서를 수정할 방법이 필요합니다. 이 단계에서는 JSON_SET 함수를 사용하여 기존 값을 업데이트하고 새 키 - 값 쌍을 추가합니다.
JSON_SET 함수는 대상 열, 필드 경로 및 새 값을 인수로 받아 JSON 문서를 수정합니다.
먼저 노트북의 price를 1200 에서 1250 으로 업데이트해 보겠습니다.
UPDATE products
SET product_details = JSON_SET(product_details, '$.price', 1250)
WHERE product_name = 'Laptop';변경 사항을 확인하려면 가격을 다시 쿼리합니다.
SELECT JSON_UNQUOTE(JSON_EXTRACT(product_details, '$.price')) AS price FROM products WHERE product_name = 'Laptop';이제 출력에 새 가격이 표시됩니다.
+-------+
| price |
+-------+
| 1250 |
+-------+
1 row in set (0.00 sec)지정된 경로가 존재하지 않으면 JSON_SET은 새 키와 값을 추가합니다. 제품에 color 속성을 추가해 보겠습니다.
UPDATE products
SET product_details = JSON_SET(product_details, '$.color', 'Silver')
WHERE product_name = 'Laptop';이제 전체 JSON 객체를 쿼리하여 새로 추가된 필드를 확인합니다.
SELECT product_details FROM products WHERE product_name = 'Laptop';출력에는 이제 color 속성이 포함된 product_details 문서가 표시됩니다.
+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| product_details |
+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| {"brand": "Dell", "color": "Silver", "model": "XPS 13", "price": 1250, "specs": {"memory": "16GB", "storage": "512GB SSD", "processor": "Intel Core i7"}} |
+-----------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)테이블 내에서 JSON 문서를 성공적으로 수정하고 확장했습니다.
테이블이 클 경우 JSON 필드를 쿼리하는 것은 느릴 수 있습니다. 성능을 개선하려면 JSON 열에서 추출된 값에 인덱스를 생성할 수 있습니다. MariaDB 에서는 먼저 JSON 필드를 기반으로 가상 열을 추가한 다음 해당 가상 열에 인덱스를 생성하여 이를 달성합니다.
이 단계에서는 price 속성에 대한 가상 열을 생성한 다음 가격 기반 쿼리를 더 빠르게 만들기 위해 인덱싱합니다.
먼저 JSON 데이터에서 가격을 추출하는 가상 열을 추가합니다.
ALTER TABLE products ADD COLUMN price_virtual INT AS (CAST(JSON_UNQUOTE(JSON_EXTRACT(product_details, '$.price')) AS SIGNED)) STORED;이 명령은 price_virtual이라는 가상 열을 추가하며, 이 열은 JSON 데이터에서 가격 값을 자동으로 계산하고 저장합니다.
이제 이 가상 열에 인덱스를 생성합니다.
CREATE INDEX idx_product_price ON products (price_virtual);이 접근 방식을 사용하면 MariaDB 가 인덱싱된 가상 열을 사용하여 숫자 가격을 기반으로 행을 효율적으로 조회할 수 있습니다.
인덱스가 생성되었는지 확인하려면 SHOW INDEXES 명령을 사용합니다.
SHOW INDEXES FROM products;출력에는 새 idx_product_price를 포함하여 products 테이블의 모든 인덱스가 나열됩니다.
+----------+------------+-------------------+...
| Table | Non_unique | Key_name |...
+----------+------------+-------------------+...
| products | 0 | PRIMARY |...
| products | 1 | idx_product_price |...
+----------+------------+-------------------+...가장 중요한 부분은 옵티마이저가 인덱스를 사용하는지 확인하는 것입니다. EXPLAIN 명령으로 이를 확인할 수 있습니다.
EXPLAIN SELECT product_name FROM products WHERE price_virtual > 1200;EXPLAIN 출력에서 possible_keys 및 key 열을 확인합니다. idx_product_price가 나열되어 MariaDB 가 인덱스를 사용하여 쿼리를 효율적으로 실행하고 있음을 확인할 수 있습니다.
원래 JSON 표현식을 사용하여 쿼리할 수도 있으며, MariaDB 옵티마이저는 여전히 가상 열의 인덱스를 사용할 수 있어야 합니다.
EXPLAIN SELECT product_name FROM products WHERE CAST(JSON_UNQUOTE(JSON_EXTRACT(product_details, '$.price')) AS SIGNED) > 1200;가상 열을 성공적으로 생성하고 JSON 속성 쿼리를 최적화하기 위해 인덱싱했습니다.
이제 MySQL 셸을 종료할 수 있습니다.
exit이 실습에서는 MariaDB 에서 JSON 데이터를 처리하는 실질적인 경험을 쌓았습니다. 데이터베이스 구조 설정부터 고급 작업 수행까지 전체 워크플로우를 배웠습니다.
구조화된 JSON 데이터를 성공적으로 삽입하고, JSON_EXTRACT 및 JSON_UNQUOTE를 사용하여 특정 필드를 쿼리하고, JSON 문서 내의 값을 기반으로 레코드를 필터링했습니다. 또한 JSON_SET을 사용하여 새 속성을 업데이트하고 추가하는 연습을 했습니다. 마지막으로 JSON 속성에 대한 가상 열을 생성하고 인덱싱하여 쿼리 성능을 개선하는 핵심 최적화 기술을 배웠습니다.
이러한 기술은 유연한 데이터베이스 스키마를 설계하고 MariaDB 에서 반구조화된 데이터를 효율적으로 관리하는 데 유용합니다.
