第205回　MySQLでJSONを活用してみる［その4］

これまで、MySQLのJSONの活用方法に関して第197回　MySQLでJSONを活用してみる［その1］、第199回　MySQLでJSONを活用してみる［その2］で簡単に紹介してきました。そして前回第202回　MySQLでJSONを活用してみる［その3］では、インデックスを使用して便利に扱う方法を紹介しました。今回は、MySQL 8.0.17から導入された複数値indexを活用して、もっと便利に扱う方法を紹介してみたいと思います。

検証環境

今回はdockerで建てたMySQLを使用します。以下のコマンドでdockerを建ててローカルからアクセスをします。

% docker run --platform linux/x86_64 -p 3307:3306 -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:latest

アクセス方法は以下の通りになります。

% mysql -uroot -pmy-secret-pw -h127.0.0.1 -P3307

執筆時点では、以下の通りMySQL 8.0.33を使用しています。

mysql>  select version();
+-----------+
| version() |
+-----------+
| 8.0.33    |
+-----------+
1 row in set (0.01 sec)

今回はtestデータベースとdataテーブルを作成します。

mysql> create database test;
mysql> use test
mysql> CREATE TABLE `json_data` (id SERIAL, doc json);

カラムには、SERIAL型のidとjson型のdocカラムを用意しています。

複数値index

複数値indexは、JSONの配列に対してindexを貼るためにMySQL 8.0.17から導入されたindexです。複数値indexを使用するためにはさまざまな制約があるのですが、JSONをうまく扱うために非常に便利なindexとなります。

今回は以下のようなJSONが格納されていると考えて、indexを貼っていきたいと思います。

{
	"string1": "string1",
	"string2": "string2",
	"array1": [1, 2],
	"array2": [3, 4]
}

実際に格納してみましょう。

mysql> INSERT INTO json_data(doc) VALUES('{"string1": "string1", "string2": "string2", "array1": [1, 2], "array2": ["3","4"]}');
mysql> INSERT INTO json_data(doc) VALUES('{"string1": "string1", "string2": "string2", "array1": ["a","b"], "array2": ["3","4"]}');

array1に対して1が含まれているJSONを検索してみるクエリを作ってみます。今回はMySQL 8.0.17で導入されたMEMBER OF 関数を使用してみます。MEMBER OF関数は、JSONの配列に対して特定の値が含まれている場合、1を返す関数となります。

mysql> SELECT 1 MEMBER OF('[1, 2]');
+-----------------------+
| 1 MEMBER OF('[1, 2]') |
+-----------------------+
|                     1 |
+-----------------------+

mysql> SELECT 3 MEMBER OF('[1, 2]');
+-----------------------+
| 3 MEMBER OF('[1, 2]') |
+-----------------------+
|                     0 |
+-----------------------+

ではこれを使用して、array1に1が含まれているjsonを検索してみようと思います。

mysql> select doc FROM json_data WHERE 1 MEMBER OF(doc->'$.array1');
+----------------------------------------------------------------------------------+
| doc                                                                              |
+----------------------------------------------------------------------------------+
| {"array1": [1, 2], "array2": [3, 4], "string1": "string1", "string2": "string2"} |
+----------------------------------------------------------------------------------+
1 row in set (0.01 sec)

このクエリに対してexplainを取ってみましょう。

mysql> explain select doc FROM json_data WHERE 1 MEMBER OF(doc->'$.array1');
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | json_data | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+

type ALLとなっており、当然ですがindexが使われていないことがわかります。

それでは実際に複数値indexを貼ってみたいと思います。今回は、先ほどのクエリに合わせてarray1に対してindexを貼ってみます。複数値indexは正の整数の配列にCASTされるので、CASTした値を指定しています。

mysql> ALTER TABLE json_data ADD INDEX json_array((CAST(doc->"$.array1" AS UNSIGNED ARRAY)));

それでは、explainを取り直して確認してみましょう。

mysql> explain select doc FROM json_data WHERE 1 MEMBER OF(doc->'$.array1');
+----+-------------+-----------+------------+------+---------------+------------+---------+-------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | json_data | NULL       | ref  | json_array    | json_array | 9       | const |    2 |   100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------------+---------+-------+------+----------+-------------+

typeもrefとなり、keyも今回作成したindexのjson_arrayが指定されているのがわかります。このように複数値indexが使用されていることがわかります。

複数値indexが使用される関数

複数値indexが使用される関数としては以下の3つの関数があります。

MEMBER OF関数
JSON_CONTAINS関数
JSON_OVERLAPS関数

それぞれに関して簡単に紹介しますが、このうちMEMBER OF関数に関しては先ほど簡単に紹介したため、割愛させていただきます。

JSON_CONTAINS関数はMEMBER OF 関数と似ており、特定の値が含まれているか確認することができる関数です。第1引数に渡されたJSONオブジェクトに対して、第2引数の値が含まれているか確認することができます。

mysql> SELECT JSON_CONTAINS('[1, 2, 3, 4]', '1');
+------------------------------------+
| JSON_CONTAINS('[1, 2, 3, 4]', '1') |
+------------------------------------+
|                                  1 |
+------------------------------------+


mysql> SELECT JSON_CONTAINS('[1, 2, 3, 4]', '0');
+------------------------------------+
| JSON_CONTAINS('[1, 2, 3, 4]', '0') |
+------------------------------------+
|                                  0 |
+------------------------------------+
1 row in set (0.00 sec)

JSON_OVERLAPS関数は少し独特な挙動をします。2つのJSONオブジェクトを引数として渡すのですが、キーと値で同一のものが1つ以上存在する場合に1を返す関数となります。今回は[1,2,3,4]という配列と[5,6,7,8]を比較してみました。この場合、共有されている値が存在しないため0が返ります。

 mysql> SELECT JSON_OVERLAPS('[1, 2, 3, 4]', '[5,6,7,8]');
+--------------------------------------------+
| JSON_OVERLAPS('[1, 2, 3, 4]', '[5,6,7,8]') |
+--------------------------------------------+
|                                          0 |
+--------------------------------------------+

続いて、[1,2,3,4]と[1]を比較してみます。[1]と[1,2,3,4]は1が共通のため、1が返ります。

mysql> SELECT JSON_OVERLAPS('[1, 2, 3, 4]', '[1]');
+--------------------------------------+
| JSON_OVERLAPS('[1, 2, 3, 4]', '[1]') |
+--------------------------------------+
|                                    1 |
+--------------------------------------+

この関数は、MEMBER OF関数と同様にMySQL 8.0.17から導入されているので注意しましょう。

まとめ

今回は、複数値indexを使ってJSONをもっと便利に扱う方法を紹介してみました。MySQL 8系はGAになってからも機能追加が積極的に行われていたので、便利に扱える関数がどんどん追加されていました。MySQL 8系に上げた場合でもバージョンによっては関数が存在しない場合があるので、使用する際には注意しましょう。