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 データ型に関して基本的な利用法を解説いたしました。