MySQL道普請便りではこれまで、FIND_IN_SETやCOALESCEなど、さまざまな関数を紹介してきました。今回は、1つの記事として単独で紹介するにはやや物足りないものの、データの調査や修正作業時に役立つかもしれない関数をいくつか取り上げます。なお、今回の検証には MySQL 8.4.3 を使用しています。

ANY_VALUE() ―非集約カラムを表示

ANY_VALUE()は、GROUP BYで非集約カラムを取得する際の制約を回避し、適当な 1 つの値を取得する関数です。MySQL 5.7以降では、sql_modeにONLY_FULL_GROUP_BYがデフォルトで有効になっており、GROUP BYのカラム以外をSELECTで使用すると、以下のようなケースでエラーになります。この問題を回避し、非集約カラムの値を1つ取得するのがANY_VALUE()です。

mysql> show create table employees\G
*************************** 1. row ***************************
       Table: employees
Create Table: CREATE TABLE `employees` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `department` varchar(50) DEFAULT NULL,
  `salary` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> SELECT department, name, SUM(salary) AS total_salary FROM employees GROUP BY department;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'd1.employees.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

この関数は、GROUP BYを使用する際に、非集約カラムをそのまま取得したいときに便利です。データの調査目的でGROUP BYを使って上記のエラーが出た場合、集約関数に対象のカラムも適用することで非集約カラムの値を取得する方法もありますが、それでは期待する結果とならないこともあります。そのようなケースでは、ANY_VALUE()を使用することでエラーを回避できます。

たとえば、以下のようなSQLでは、departmentごとにSUM(salary)を計算しながら、適当な1つのnameを取得できます。

mysql> SELECT department, ANY_VALUE(name) AS sample_employee, SUM(salary) AS total_salary FROM employees GROUP BY department;
+-------------+-----------------+--------------+
| department  | sample_employee | total_salary |
+-------------+-----------------+--------------+
| Engineering | Alice           |        13000 |
| HR          | Charlie         |        10500 |
| Marketing   | Eve             |         9200 |
+-------------+-----------------+--------------+
3 rows in set (0.00 sec)

ただし、ANY_VALUE()で取得される値は不定であるため、あくまで参考値として利用することを認識しておく必要があります。

FIELD() ―特定の値の順位を取得

FIELD()は、指定したリストの中で 特定の値が1から始まるインデックスで何番目にあるか を取得する関数です。特定の値がリストの何番目にあるのかを知りたいときに使用します。構文は以下の通りです。

FIELD(string, string1, string2, string3, ...)

この場合、stringがstring1, string2, string3, ...のリスト内に含まれている場合、そのインデックス番号（1から始まる）を返します。たとえば、次のSQLでは、第一引数の"banana"はリスト内の2番目にあるため、2を返します。

mysql> SELECT FIELD('banana', 'apple', 'banana', 'orange') as fruits;
+--------+
| fruits |
+--------+
|      2 |
+--------+
1 row in set (0.00 sec)

FIELD()を使うと、特定の順番をカスタム定義できるのが最大の特徴です。通常のORDER BYでは、昇順・降順（アルファベット順・数値順）のみで並び替えられます。しかし、ORDER BY 句で FIELD()を使用することで、任意の順番を指定し、並び替えを制御することが可能になります。

たとえば、以下のようなテーブルを例に考えてみましょう。

mysql> CREATE TABLE tasks (
    ->     id INT AUTO_INCREMENT PRIMARY KEY,
    ->     task_name VARCHAR(50),
    ->     priority VARCHAR(10)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO tasks (task_name, priority) VALUES
    -> ('Fix security bug', 'High'),
    -> ('Write documentation', 'Low'),
    -> ('Refactor code', 'Medium'),
    -> ('Deploy new feature', 'High'),
    -> ('Optimize database', 'Medium');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

通常の ORDER BY priority を使用すると、アルファベット順の昇順または降順 で並び替えられるため、優先度（priority）の高い順（High, Medium, Low）で並び替えることはできません。

mysql> SELECT priority, count(distinct task_name) FROM tasks GROUP BY priority ORDER BY priority ASC;
+----------+---------------------------+
| priority | count(distinct task_name) |
+----------+---------------------------+
| High     |                         2 |
| Low      |                         1 |
| Medium   |                         2 |
+----------+---------------------------+
3 rows in set (0.00 sec)

しかし、ORDER BY句でFIELD()関数を使用することで、優先度の高い順にデータを並び替えることが可能になります。

mysql> SELECT priority, COUNT(DISTINCT task_name)
    -> FROM tasks
    -> GROUP BY priority
    -> ORDER BY FIELD(priority, 'High', 'Medium', 'Low');
+----------+---------------------------+
| priority | COUNT(DISTINCT task_name) |
+----------+---------------------------+
| High     |                         2 |
| Medium   |                         2 |
| Low      |                         1 |
+----------+---------------------------+
3 rows in set (0.00 sec)

JSON_MERGE_PRESERVE()/JSON_MERGE_PATCH() ―JSONのマージ

JSON_MERGE_PRESERVE()とJSON_MERGE_PATCH()は、複数のJSONデータを統合するための関数です。どちらもJSONのマージを行いますが、統合対象のデータに同じキーが存在する場合の挙動が異なります。

MySQL 5.7以降、JSONデータ型が追加され、JSONデータの保存や操作にJSON型を利用するユーザーが増えつつあります。特に、JSONデータの修正を行う際に、これらの関数は非常に便利です。

JSON_MERGE_PRESERVE()

JSON_MERGE_PRESERVE()は、複数のJSONドキュメントを1つのJSONに統合（マージ）し、結果を返す関数です。引数のいずれかがNULLの場合はNULLを返します。

マージは以下のルールに基づいて実行されます

• 配列同士の場合：単一の配列に統合
• JSON オブジェクトの場合：単一のオブジェクトに統合
• スカラー値の場合：配列として統合
• 配列とオブジェクトをマージする場合：オブジェクトを配列として自動ラップし、2つの配列を統合

mysql> SELECT JSON_MERGE_PRESERVE('{"a": 1, "b": [2]}','{"c": 3}', '{"a": "4"}');
+--------------------------------------------------------------------+
| JSON_MERGE_PRESERVE('{"a": 1, "b": [2]}','{"c": 3}', '{"a": "4"}') |
+--------------------------------------------------------------------+
| {"a": [1, "4"], "b": [2], "c": 3}                                  |
+--------------------------------------------------------------------+
1 row in set (0.00 sec)

JSON_MERGE_PRESERVE()は、JSONオブジェクトのキーが重複する場合には配列としてマージされて追加されるため、マージするときにログや履歴など過去の情報を保持しておく必要がある場合に便利です。この関数はJSON_MERGE()という関数のシノニムですが、JSON_MERGE関数は現在非推奨です。

JSON_MERGE_PATCH()

JSON_MERGE_PRESERVE()は、キーが重複した際に配列にラップして値を保持します。しかし、場合によっては最新の情報のみを保持したいこともあります。こうしたケースでは、JSON_MERGE_PATCH() が便利です。

この関数は基本的な動作はJSON_MERGE_PRESERVE()と同じですが、キーが重複した場合、後の値で上書きされます。また、後のJSONに同じキーがあり、その値がnullの場合（JSONのnull⁠）⁠、そのキーは削除されます。ただし、SQLのNULLが引数として渡された場合、結果はNULLになります。

以下の例では、JSON_MERGE_PRESERVE()を使用したSQLの関数部分をJSON_MERGE_PATCH()に置き換えたものです。この結果、引数の1番目と3番目にあるJSONオブジェクトのキーaが重複している部分が配列ではなく4に上書きされ、1つのJSONオブジェクトとして統合されていることが確認できます。

mysql> SELECT JSON_MERGE_PATCH('{"a": 1, "b": [2]}','{"c": 3}', '{"a": "4"}');
+-----------------------------------------------------------------+
| JSON_MERGE_PATCH('{"a": 1, "b": [2]}','{"c": 3}', '{"a": "4"}') |
+-----------------------------------------------------------------+
| {"a": "4", "b": [2], "c": 3}                                    |
+-----------------------------------------------------------------+
1 row in set (0.00 sec)

また、以下のように重複したキーaの値がnullの場合は、マージ後にキーaが削除されて表示されることが確認できます。

mysql> SELECT JSON_MERGE_PATCH('{"a": 1, "b": [2]}','{"c": 3}', '{"a": null}');
+------------------------------------------------------------------+
| JSON_MERGE_PATCH('{"a": 1, "b": [2]}','{"c": 3}', '{"a": null}') |
+------------------------------------------------------------------+
| {"b": [2], "c": 3}                                               |
+------------------------------------------------------------------+
1 row in set (0.00 sec)

まとめ

今回は、データの調査や修正作業に役立つ可能性のある関数として、ANY_VALUE()、FIELD()、JSON_MERGE_PRESERVE()、JSON_MERGE_PATCH()の4つを紹介しました。これらの関数を知らなくても、SQLの結果をプログラミング言語で加工することで同様の処理を実現できる場合があります。しかし、関数を活用することで、SQL内で処理を完結できるケースもあります。利用シーンは限定的かもしれませんが、機会があればぜひ活用してみてください。

各関数の詳細な動作については、以下の公式ドキュメントを参考にしてください。

• ANY_VALUE()関数
• FIELD()関数
• JSON_MERGE_PATCH()/JSON_MERGE_PRESERVE()関数
• JSON_MERGE_PATCH() と JSON_MERGE_PRESERVE() の比較