MySQL での JSON の操作

現代のアプリケーション開発では、半構造化データの管理が共通の課題となっています。バージョン 5.7 で導入された MySQL の JSON サポートは、リレーショナル データベース内でそのようなデータを保存、クエリ、操作するための強力な方法を提供します。この投稿では、MySQL が提供する重要な JSON 関数について、開始に役立つ実用的な例を示しながら説明します。

MySQL で JSON を使用する理由

JSON データをリレーショナル データベースに保存すると、半構造化データまたは階層データの処理が簡素化されます。次のことが可能になります:

• 柔軟性: JSON により、動的で階層的なデータ ストレージが可能になります。
• 組み込み関数: JSON データを効率的にクエリ、更新、検証します。
• 統合: ハイブリッド ユースケース向けに、リレーショナル データと JSON オブジェクトを組み合わせます。

1. JSONデータの作成

JSON_OBJECT() 関数と JSON_ARRAY() 関数を使用して、JSON オブジェクトまたは配列を構築できます。

例:

SELECT 
    JSON_OBJECT('id', 1, 'name', 'Alice', 'roles', JSON_ARRAY('admin', 'editor')) AS json_data;

出力:

{"id": 1, "name": "Alice", "roles": ["admin", "editor"]}

2. JSONデータの保存

JSON データを保存するには、列に JSON データ型を使用します。

例:

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    details JSON NOT NULL
);

INSERT INTO users (details) 
VALUES ('{"name": "Bob", "age": 30, "roles": ["viewer", "editor"]}');

3. JSON からのデータの抽出

MySQL には、JSON ドキュメントからデータを抽出するためのいくつかの関数が用意されています。

• JSON_EXTRACT(): JSON パスを使用して値を取得します。
• ->演算子: JSON_EXTRACT() の短縮形。

例:

SELECT 
    JSON_EXTRACT(details, '$.name') AS name,
    details->'$.age' AS age
FROM users;

出力:

4. JSONデータの変更

次のコマンドを使用して、JSON データに要素を更新または追加できます。

• JSON_SET(): キーを挿入または更新します。
• JSON_INSERT(): キーが存在しない場合に挿入します。
• JSON_REPLACE(): 既存のキーのみを更新します。

例:

UPDATE users
SET details = JSON_SET(details, '$.city', 'New York')
WHERE id = 1;

SELECT details FROM users;

出力:

{"name": "Bob", "age": 30, "roles": ["viewer", "editor"], "city": "New York"}

5. キーまたは値の削除

JSON_REMOVE() を使用して、JSON ドキュメントから要素を削除します。

例:

UPDATE users
SET details = JSON_REMOVE(details, '$.roles')
WHERE id = 1;

SELECT details FROM users;

出力:

{"name": "Bob", "age": 30, "city": "New York"}

6. JSONでの検索

JSON_CONTAINS() 関数は、JSON ドキュメントに特定の値が含まれているかどうかを確認します。

例:

SELECT 
    JSON_CONTAINS(details, '"New York"', '$.city') AS has_city
FROM users;

出力:

7. JSONデータの集約

JSON_ARRAYAGG() 関数と JSON_OBJECTAGG() 関数は、クエリ結果を JSON 構造に集約するのに役立ちます。

例:

SELECT 
    JSON_ARRAYAGG(name) AS names
FROM (
    SELECT JSON_EXTRACT(details, '$.name') AS name FROM users
) AS subquery;

出力:

SELECT 
    JSON_OBJECT('id', 1, 'name', 'Alice', 'roles', JSON_ARRAY('admin', 'editor')) AS json_data;

8. JSON データの検証

JSON_VALID() 関数は、文字列が有効な JSON かどうかをチェックします。

例:

{"id": 1, "name": "Alice", "roles": ["admin", "editor"]}

出力:

9. JSON をきれいに印刷する

人間が判読できる JSON 形式にするには、JSON_PRETTY() を使用します。

例:

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    details JSON NOT NULL
);

INSERT INTO users (details) 
VALUES ('{"name": "Bob", "age": 30, "roles": ["viewer", "editor"]}');

出力:

SELECT 
    JSON_EXTRACT(details, '$.name') AS name,
    details->'$.age' AS age
FROM users;

追加の JSON 関数

MySQL は、詳細に説明したもの以外にも幅広い JSON 関数を提供します。以下は他の JSON 関数です:

• JSON_ARRAY_APPEND(): JSON 配列の末尾に値を追加します。
• JSON_ARRAY_INSERT(): JSON 配列の指定された位置に値を挿入します。
• JSON_CONTAINS_PATH(): 指定されたパスが JSON ドキュメント内に存在するかどうかを確認します。
• JSON_DEPTH(): JSON ドキュメントの最大の深さを返します。
• JSON_KEYS(): JSON オブジェクトからキーを抽出します。
• JSON_LENGTH(): JSON 配列内の要素の数、または JSON オブジェクト内のキーの数を計算します。
• JSON_MERGE_PATCH(): 複数の JSON ドキュメントを結合し、既存のキーを最新の値で上書きします。
• JSON_MERGE_PRESERVE(): 複数の JSON ドキュメントを結合し、重複の配列を作成することですべての値を保持します。
• JSON_OVERLAPS(): 2 つの JSON ドキュメントに重複する要素があるかどうかを判断します。
• JSON_QUOTE(): 文字列内の特殊文字をエスケープし、有効な JSON 文字列リテラルとして返します。
• JSON_SEARCH(): JSON ドキュメント内の指定された値へのパスを返します。
• JSON_STORAGE_FREE(): JSON 列の変更後に解放されたスペースの量を返します。
• JSON_STORAGE_SIZE(): JSON ドキュメントのストレージ サイズをバイト単位で提供します。
• JSON_TABLE(): さらなる処理のために JSON データをリレーショナル テーブル形式に変換します。
• JSON_TYPE(): JSON 値のタイプ (オブジェクト、配列、文​​字列など) を返します。
• JSON_UNQUOTE(): JSON 文字列から引用符を削除します。

MySQL の JSON 関数は、リレーショナル データベース内の半構造化データを管理するための強力なツールを提供します。これらにより、SQL 内で JSON を直接保存、クエリ、操作することが簡単になります。これらの機能を理解することは、ワークフローを簡素化し、データベース設計への新しいアプローチを開くのに役立ちます。

以上がMySQL での JSON の操作の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。