JSON を扱う – MariaDB Server

spacer

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

お問い合わせ