MySQLにおけるJSONフィールドのクエリと配列変換

現代のWebアプリケーション開発において、JSON形式はデータの保存と転送に広く利用されています。MySQLデータベースではJSONデータ型をサポートしており、複雑なデータ構造を効率的に格納できます。本記事では、MySQLのJSONフィールドからデータを抽出し、配列形式に変換する手法について解説します。

MySQLでのJSONフィールドの基本

MySQL 5.7以降、JSONデータ型のサポートが導入されました。JSONフィールドを使用することで、オブジェクト、配列、文字列などの多様なデータ型をデータベース内で直接操作できます。構造化データの保存と処理に最適な選択肢となります。

テーブル作成時にJSONフィールドを定義する構文は以下の通りです：

CREATE TABLE employee_profile (
    employee_id INT PRIMARY KEY,
    full_name VARCHAR(100),
    attributes JSON
);

この例では、attributesフィールドがJSON型として定義され、任意のJSON形式のデータを格納できます。

JSONデータの抽出と配列への変換

JSONフィールド内のデータを抽出して配列として扱うには、MySQLのJSON関数を活用します。以下に具体例を示します。次のようなレコードが存在すると仮定します：

certificationsフィールドの値を配列として取得するには、以下のSQLクエリを実行します：

SELECT 
    employee_id,
    full_name,
    JSON_EXTRACT(attributes, '$.certifications') AS certification_list
FROM 
    employee_profile
WHERE 
    employee_id = 101;

このクエリでは、JSON_EXTRACT関数を使用してJSONフィールドからデータを抽出します。'$.certifications'はcertificationsキーの値を抽出するためのパス指定です。実行結果は以下のようになります：

さらに、JSON配列を個別の行として展開するにはJSON_TABLE関数を使用できます：

SELECT 
    ep.employee_id,
    ep.full_name,
    cert_item.cert_name
FROM 
    employee_profile ep,
    JSON_TABLE(
        ep.attributes,
        '$.certifications[*]' 
        COLUMNS (
            cert_name VARCHAR(200) PATH '$'
        )
    ) AS cert_item
WHERE 
    ep.employee_id = 101;

高度なJSON操作の例

より複雑なJSON構造の場合、複数の関数を組み合わせてデータを処理できます。例えば、JSON配列の要素数を取得するにはJSON_LENGTH関数を使用します：

SELECT 
    employee_id,
    full_name,
    JSON_LENGTH(attributes, '$.certifications') AS certification_count
FROM 
    employee_profile;

JSON配列に新しい要素を追加するにはJSON_ARRAY_APPEND関数を利用できます：

UPDATE employee_profile
SET attributes = JSON_ARRAY_APPEND(
    attributes, 
    '$.certifications', 
    'Google Cloud認定エンジニア'
)
WHERE employee_id = 101;

これらのJSON関数を適切に組み合わせることで、データベースレベルでJSONデータを効率的に操作し、アプリケーション側での処理負担を軽減できます。