MySQLでJSON型で返す方法

MySQL SQL DB

MySQLでJSON型で返す方法

MySQLでJSON型でデータを返すためには、JSON関数やJSON_ARRAY、JSON_OBJECTなどを活用します。
MySQL 5.7以降では、JSONデータ型が公式にサポートされており、JSONデータを格納し、取得し、処理する機能が提供されています。
以下に、MySQLでJSON型を返すための基本的な方法と応用例を紹介します。

1. 基本的な使い方: JSON_OBJECT 関数

JSON_OBJECT関数は、キーと値のペアからJSONオブジェクトを生成します。
例えば、テーブルの特定のカラムの値をJSON形式で返すには次のようにします。

SELECT JSON_OBJECT('id', id, 'name', name, 'age', age)
FROM users;

このクエリでは、usersテーブルからid、name、ageのカラムをJSON形式で取得します。
結果は以下のようなJSONオブジェクトとして返されます。

{"id": 1, "name": "Alice", "age": 25}
{"id": 2, "name": "Bob", "age": 30}

この方法では、カラム名を任意のキーとして指定できるため、レスポンスの柔軟なカスタマイズが可能です。

2. 複数行をJSON配列で返す: JSON_ARRAYAGG 関数

MySQLで複数の行を1つのJSON配列として返したい場合は、JSON_ARRAYAGG関数を使用します。
例えば、usersテーブルの全行を1つのJSON配列としてまとめたい場合、次のクエリを使用します。

SELECT JSON_ARRAYAGG(JSON_OBJECT('id', id, 'name', name, 'age', age)) AS user_data
FROM users;

このクエリの結果は、すべてのユーザーを含む1つのJSON配列として返されます。

[
  {"id": 1, "name": "Alice", "age": 25},
  {"id": 2, "name": "Bob", "age": 30}
]

この方法を使用することで、複数のレコードを一つのJSONレスポンスにまとめることができ、APIレスポンスとして便利です。

3. JSON型カラムの利用

MySQL 5.7以降では、JSONデータ型をテーブルのカラムとして定義することができます。
JSONデータ型のカラムにデータを格納し、クエリでJSON形式のデータを直接操作することも可能です。
例えば、次のようにテーブルを作成します。

CREATE TABLE orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  customer_name VARCHAR(100),
  order_details JSON
);

この例では、order_detailsカラムがJSON型として定義されています。
このカラムにJSON形式のデータを挿入するには、次のようなクエリを使用します。

INSERT INTO orders (customer_name, order_details)
VALUES ('Alice', '{"product": "Laptop", "quantity": 1, "price": 1200}');

JSON型カラムから特定のキーの値を取得するには、->>演算子を使用します。
例えば、order_detailsからproductフィールドを取得するには次のようにします。

SELECT customer_name, order_details->>'$.product' AS product
FROM orders;

このクエリは、各注文のcustomer_nameとproductを返します。

4. JSONデータの更新

JSONデータ型を使用している場合、JSONの特定のフィールドを更新する必要がある場合もあります。
MySQLでは、JSON_SET関数を使用してJSONデータのフィールドを更新することができます。
例えば、order_detailsのpriceフィールドを1500に更新するには次のようにします。

UPDATE orders
SET order_details = JSON_SET(order_details, '$.price', 1500)
WHERE customer_name = 'Alice';

このクエリにより、Aliceの注文に含まれるpriceフィールドが1500に更新されます。

5. JSONデータの検索

JSONデータの中で特定の値を検索することもできます。
たとえば、order_detailsに特定のproductを持つ行を検索する場合、JSON_CONTAINS関数を使用します。

SELECT * FROM orders
WHERE JSON_CONTAINS(order_details, '{"product": "Laptop"}');

このクエリは、order_detailsに"product": "Laptop"が含まれる行を返します。

6. JSON_UNQUOTEで文字列として扱う

JSONデータを扱う際に、JSON_UNQUOTE関数を使用してJSON値をアンエスケープし、通常の文字列として取得することも可能です。

SELECT JSON_UNQUOTE(order_details->>'$.product') AS product
FROM orders;

このクエリは、JSONデータ内のproductフィールドを文字列として返します。

まとめ

MySQLでJSON型を返す方法として、JSON_OBJECT、JSON_ARRAYAGGなどのJSON関連関数を使うことで、データを柔軟にJSON形式で整形できます。
さらに、JSON型カラムを用いてデータベースにJSONデータを直接格納し、そのデータに対する検索や更新も行うことができます。
APIなどのレスポンス形式としてJSONが広く使われる中で、MySQLのJSON機能を活用することで、効率的なデータ操作とレスポンス生成が可能になります。