はじめに
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 列で定義してあります。
|
1 2 3 4 5 6 7 |
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 制約で用いることでデータをチェックすることができます。
|
1 2 3 4 5 6 7 8 |
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 関数をテストしてみます。
|
1 2 3 4 5 6 7 8 |
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 <code>CONSTRAINT_1</code> failed for <code>test</code>.<code>products |
最後の INSERT 文における JSON データでは，white 直後の " (二重引用符) が欠落しており，エラーが発生していますので，CHECK 制約は正常に機能しているようです。
訂正したデータを INSERT してみます。
|
1 2 3 4 5 6 7 8 9 10 11 |
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 属性に対する仮想列が必要となります。
以下のように仮想列とインデックスを追加することができます。
|
1 2 |
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 列で検索を行ってみます。
|
1 2 3 4 5 6 7 |
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 で実行計画を確認してみます。
|
1 2 3 4 5 6 |
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 関数を用います。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
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 データ型に関して基本的な利用法を解説いたしました。
