---
title: "JSON を扱う – MariaDB Server"
publish_date: 2019-05-28
updated_date: 2023-10-12
author: "MariaDB"
tags:
  - name: "JSON"
    url: "/ja/resources/blog/tag/json-ja.md"
  - name: "MariaDB Server"
    url: "/ja/resources/blog/tag/mariadb-server-ja.md"
---

# JSON を扱う – MariaDB Server

MariaDB Server では，バージョン 10.2 以降 [JSON データ型](https://staging-mdb.com/kb/en/library/json-data-type/) が追加され，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](https://staging-mdb.com/kb/en/library/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](https://staging-mdb.com/kb/en/library/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](https://staging-mdb.com/kb/en/library/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 データ型に関して基本的な利用法を解説いたしました。

[お問い合わせ](https://staging-mdb.com/ja/contact/)