JSON を扱う – MariaDB Server
MariaDB Server では，バージョン 10.2 以降 JSON データ型 が追加され，MySQL の JSON データ型との互換性のために LONGTEXT データ型 への alias となっています。
今回は MariaDB Server における JSON データの基本的な扱い方を解説致します。
実行環境
- MariaDB Server 10.3.14
- CentOS 7.6.1810
サンプルテーブルの作成
今回は衣類を販売する店舗における在庫管理テーブルのようなものを作成してみます。
このテーブルには，品名(name)，単価(price)，在庫数(stock)等，テーブル内のすべての行に存在するデータがあり，従来どおりスキーマで定義されています。
これに対して，品目ごとに異なる意味を持つ属性(色，長さ，サイズ等)を JSON データ型の attr 列で定義してあります。
CREATE TABLE products ( id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT, name VARCHAR(255) NOT NULL, price DECIMAL(10,0) NOT NULL, stock INTEGER NOT NULL, attr JSON );
ここで，attr 列は内部的には longtext 型であり，無効な JSON データであっても格納できてしまうという問題があります。
この問題に対する方策としては，以下のように JSON_VALID 関数を CHECK 制約で用いることでデータをチェックすることができます。
CREATE TABLE products ( id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT, name VARCHAR(255) NOT NULL, price DECIMAL(10,0) NOT NULL, stock INTEGER NOT NULL, attr JSON, CHECK (JSON_VALID(attr)) );
正当な JSON データと 不正な JSON データを INSERT し，JSON_VALID 関数をテストしてみます。
MariaDB [test]> INSERT INTO products VALUES(NULL, 'Jeans', 6478, 500, NULL);
Query OK, 1 row affected (0.004 sec)
MariaDB [test]> INSERT INTO products VALUES(NULL, 'Shirt', 4999, 30, '{"size": 42, "color": "white"}');
Query OK, 1 row affected (0.002 sec)
MariaDB [test]> INSERT INTO products VALUES(NULL, 'Blouse', 6469, 25, '{"color": "white}');
ERROR 4025 (23000): CONSTRAINT `CONSTRAINT_1` failed for `test`.`products`
最後の INSERT 文における JSON データでは，whiteの後ろの “(二重引用符) が欠落しており，エラーが発生していますので，CHECK 制約は正常に機能しているようです。
訂正したデータを INSERT してみます。
MariaDB [test]> INSERT INTO products VALUES(NULL, 'Blouse', 6469, 25, '{"color": "white"}');
Query OK, 1 row affected (0.001 sec)
MariaDB [test]> SELECT * FROM products;
+----+--------+-------+-------+--------------------------------+------------+
| id | name | price | stock | attr | attr_color |
+----+--------+-------+-------+--------------------------------+------------+
| 1 | Jeans | 6478 | 50 | NULL | NULL |
| 2 | Shirt | 4999 | 30 | {"size": 42, "color": "white"} | white |
| 3 | Blouse | 6469 | 25 | {"color": "red"} | white |
+----+--------+-------+-------+--------------------------------+------------+
問題なくデータを INSERT することができました。
JSONデータへのインデックスの作成
JSON 文字列中の個々の属性データにインデックスを作成するには，Virtual Column (仮想列) を用いることで可能です。
color 属性のインデックスを作成するには，attr 列から JSON_VALUE 関数で抽出された color 属性に対する仮想列が必要となります。
以下のように仮想列とインデックスを追加することができます。
MariaDB> ALTER TABLE products ADD attr_color VARCHAR(32) AS (JSON_VALUE(attr, '$.color')); MariaDB> CREATE INDEX products_attr_color_idx ON products(attr_color);
attr_color 列で検索を行ってみます。
MariaDB [test]> SELECT * FROM products WHERE attr_color = 'white';
+----+--------+-------+-------+--------------------------------+------------+
| id | name | price | stock | attr | attr_color |
+----+--------+-------+-------+--------------------------------+------------+
| 2 | Shirt | 4999 | 30 | {"size": 42, "color": "white"} | white |
| 3 | Blouse | 6469 | 25 | {"color": "white"} | white |
+----+--------+-------+-------+--------------------------------+------------+
さらに EXPLAIN で実行計画を確認してみます。
MariaDB [test]> EXPLAIN SELECT * FROM products WHERE attr_color = 'white'; +------+-------------+----------+------+-------------------------+-------------------------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+----------+------+-------------------------+-------------------------+---------+-------+------+-------------+ | 1 | SIMPLE | products | ref | products_attr_color_idx | products_attr_color_idx | 35 | const | 2 | Using where | +------+-------------+----------+------+-------------------------+-------------------------+---------+-------+------+-------------+
JSON データの更新
ブラウス(Blouse)の色を白から赤に更新し，attr_color 列 が attr 列に依存していることを確認してみます。
JSON オブジェクトの値を置換するには，JSON_REPLACE 関数を用います。
MariaDB [test]> UPDATE products SET attr = JSON_REPLACE(attr, '$.color', 'red') WHERE name = 'Blouse';
Query OK, 1 row affected (0.001 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [test]> SELECT * FROM products;
+----+--------+-------+-------+--------------------------------+------------+
| id | name | price | stock | attr | attr_color |
+----+--------+-------+-------+--------------------------------+------------+
| 1 | Jeans | 6478 | 50 | NULL | NULL |
| 2 | Shirt | 4999 | 30 | {"size": 42, "color": "white"} | white |
| 3 | Blouse | 6469 | 25 | {"color": "red"} | red |
+----+--------+-------+-------+--------------------------------+------------+
MariaDB [test]> SELECT attr_color FROM products WHERE name = 'blouse';
+------------+
| attr_color |
+------------+
| red |
+------------+
正常にブラウスの色が赤に変更されていることが確認できました。
まとめ
今回は MariaDB Server 10.2 以降でサポートされている JSON データ型に関して基本的な利用法を解説いたしました。
