はじめに
この実験 (Lab) では、MySQL で JSON データ型を効果的に使用する方法を学びます。JSON ドキュメントの挿入、JSON_EXTRACT 関数や ->> 演算子を使用した特定のフィールドのクエリ、JSON 列内のデータの変更、JSON プロパティへのインデックス作成によるクエリの最適化といった基本的な操作を行います。
この実験 (Lab) を通して、MySQL サーバーに接続し、専用のデータベースとテーブルを作成し、リレーショナルデータベースのコンテキスト内で JSON データを管理するスキルを構築するための実践的なタスクを数多く実行します。
MySQL への接続とデータベースの作成
最初のステップとして、MySQL サーバーに接続し、この実験 (Lab) に必要なデータベースとテーブルを設定します。
まず、デスクトップからターミナルを開きます。
root ユーザー権限で MySQL サーバーに接続します。この実験 (Lab) 環境では、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
);
このステートメントは、3 つの列を持つテーブルを定義します。
id: 各レコードの一意の自動インクリメント整数。product_name: 製品名の文字列。product_details: 構造化データを保持するためのJSON列。
これで、必要なデータベースとテーブルの設定が完了しました。次のステップのために MySQL シェルを開いたままにしておいてください。
JSON データの挿入とクエリ
テーブルが作成されたので、次に 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;
出力には、先ほど挿入した行が表示されるはずです。JSON データが product_details 列にどのように格納されているかに注目してください。
+----+--------------+--------------------------------------------------------------------------------------------------------------------------------+
| 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 でデータを格納することは有用ですが、その中の個々のフィールドをクエリできる必要もあります。このステップでは、JSON_EXTRACT 関数と JSON_UNQUOTE を使用して、product_details 列から特定の値を抽出します。
JSON_EXTRACT 関数は、パス式を使用して 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 ドキュメントを変更する方法が必要です。このステップでは、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';
次に、製品全体をクエリして、新しく追加されたフィールドを確認します。
SELECT product_details FROM products WHERE product_name = 'Laptop';
出力には product_details ドキュメントが表示され、これには color プロパティが含まれています。
+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| 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 フィールドのクエリは遅くなる可能性があります。パフォーマンスを向上させるために、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 で半構造化データを効率的に管理するために非常に役立ちます。
