※本記事は2016年5月31日に掲載した記事を一部再編集して更新したものです。執筆時点の技術情報をベースにしています。
「SQL vs NoSQL: The Differences」で紹介したように、SQLとNoSQLの境界線は、両言語が他方の特徴を取り入れる傾向にあるため、一層分かりにくくなってきています。MySQL 5.7 InnoDBおよびPostgreSQL 9.4データベースは、どちらも1つのフィールド上でJSONを直接サポートしています。 本記事では、MySQLでJSONを扱う方法について詳しく説明します。
（Postgre SQLはバージョン9.4以前からJSONをサポートしており、すべてのデータベースがJSONを単一の文字列のBLOB型として格納できます。しかし現在では、MySQLやPostgreSQLは直接、有効なJSONデータを基本的な文字列ではなく実際のキーと値の組み合わせでサポートしています）
JSONを使えるからといって…
JSONを使えるからといって、必ずしもJSONを使うべきではありません。
正規化は、データベース構造の最適化のために使用される手法です。第1正規化は、1レコード（行）中に特定の項目が繰り返されたり、連結した値が含まれたりしない1つだけの値を含むと規定していますが、複数の値を有するJSONを使うと、このルールを破ってしまうことになります。
リレーショナルデータの要件が明確な場合は、1つの値に1つの適切なフィールドを使います。JSONの利用は、補足的な最後の手段だと考えてください。JSONはインデックス化できないため、定期的に更新、検索されるカラムでの利用は控えてください。加えて、JSONをサポートするクライアントアプリケーションは少なく、新しい技術を使う必要もあるので動作が不安定なことがあります。
そうは言っても、データ内容が不揃いである場合や、属性をカスタマイズする場合などJSONを利用すると便利なケースもあります。
JSONフィールドを使ってテーブルを作成する
書店を例に考えてください。書籍には、ID、ISBN、出版社、総ページ数、すべての書籍に割り当てられる関連データが記載されています。たとえば、本に何らかのカテゴリタグを付けたい場合を考えると分かりやすいです。以下のようなSQLの機能を利用します。
- 個別のIDに対してのタグ名を保存しているタグテーブル
- 書籍のIDをタグIDにマッピングしている多対多レコードを有するタグマップテーブル
こうすれば大丈夫でしょう。しかし、この作業は煩わしく、細かな機能を利用するには相当な労力を要します。そのため、MySQLデータベースのbookテーブルにあるJSONフィールドにタグの定義付けをします。
CREATE TABLE `book` (
`id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(200) NOT NULL,
`tags` json DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;主キーとして利用するにしても、外部キーとして参照するにしても、JSONカラムには初期値またはインデックスがないことに注意してください。生成された仮想カラムのセカンダリインデックスを作成できますが、別フィールドにインデックス化された値を置いておく方が簡単です。
JSONデータを追加する
JSONは、INSERTまたはUPDATE文で渡せます。たとえば、bookタグは、次のような配列で渡せます。
INSERT INTO `book` (`title`, `tags`)
VALUES (
'ECMAScript 2015: A SitePoint Anthology',
'["JavaScript", "ES2015", "JSON"]'
);JSONは、以下の方法でも作成可能です。
- JSON_ARRAY()は、配列に変換します。
-
-- returns [1, 2, "abc"]: SELECT JSON_ARRAY(1, 2, 'abc'); - JSON_OBJECT()は、オブジェクトを作ります。
-
-- returns {"a": 1, "b": 2}: SELECT JSON_OBJECT('a', 1, 'b', 2); - JSON_MERGE()は、データを結合します。
-
-- returns ["a", 1, {"key": "value"}]: SELECT JSON_OBJECT('["a", 1]', '{"key": "value"}'); - CAST anyValue AS JSONでも作成できます。
JSON_TYPE()を使えば、JSON値の型を確認できます。通常は、OBJECTやARRAYまたはエラーを返します。以下はその例です。
-- returns ARRAY:
SELECT JSON_TYPE('[1, 2, "abc"]');
-- returns OBJECT:
SELECT JSON_TYPE('{"a": 1, "b": 2}');
-- returns an error:
SELECT JSON_TYPE('{"a": 1, "b": 2');同様に、JSONが有効であればJSON_VALID()は1を返します。
-- returns 1:
SELECT JSON_TYPE('[1, 2, "abc"]');
-- returns 1:
SELECT JSON_TYPE('{"a": 1, "b": 2}');
-- returns 0:
SELECT JSON_TYPE('{"a": 1, "b": 2');無効なJSONの挿入を試みると、エラーが発生して、すべてのレコードが挿入されるか、更新されません。
JSONデータの検索
JSON_CONTAINS()は、検索対象のJSONと他を比較し、合致すると1を返します。以下は例です。
-- all books with the 'JavaScript' tag:
SELECT * FROM `book`
WHERE JSON_CONTAINS(tags, '["JavaScript"]');同様に、JSON_SEARCH()は検索対象のJSONと他を比較し、合致するとJSONPath、見つからない場合はNULLを返します。引数「one」で最初に合致したもの、「all」で合致したものすべてを返します。以下はその例です。
-- all books with tags starting 'Java':
SELECT * FROM `book`
WHERE JSON_SEARCH(tags, 'one', 'Java%') IS NOT NULL;JSONPath
JSONPathはJSONデータを部分的に抽出したり、修正したりするために、利用する場合があります。JSON_EXTRACT()は、1つ以上の値を抽出して表示します。
-- returns "SitePoint":
SELECT JSON_EXTRACT(
'{"id": 1, "website": "SitePoint"}',
'$.website'
);JSONPathの定義はすべて、$で始まり、その後にセレクターが続きます。
- ピリオドに名称が続きます。例えば、$.websiteです。
- [N]は、ゼロインデックス配列内にあるNです。
- .[*]ワイルドカードは、オブジェクトに当てはまるすべてのメンバーを求めます。
- [*]ワイルドカードは、配列に当てはまるすべてのメンバーを求めます。
- prefix**suffixワイルドカードは、名称のついた接頭辞で始まり、名称のついた接尾辞で終わるすべてのPathを求めます。
以下のJSONを参照してください。
{
"a": 1,
"b": 2,
"c": [3, 4],
"d": {
"e": 5,
"f": 6
}
}Pathは次のようになります
- $.a returns 1
- $.c returns [3, 4]
- $.c[1] returns 4
- $.d.e returns 5
- $**.e returns [5]
クエリーでJSONPathを抽出
クエリーを使ってbookテーブルから第1タグを抽出できます。
SELECT
name,
tags->"$[0]" AS `tag1`
FROM `book`;これまでの説明より複雑な例としては、JSONプロファイルデータの入ったユーザーテーブルなどを考えてみてください。
| id | name | profile |
| 1 | Craig | { “twitter”: “@craigbuckler”, “facebook”: “craigbuckler”, “googleplus”: “craigbuckler” } |
| 2 | SitePoint | { “twitter”: “@sitepointdotcom” } |
JSONPathを利用してTwitterの名前を抽出できます。以下はその例です。
SELECT
name, profile->"$.twitter" AS `twitter`
FROM `user`;WHERE節を使って、JSONPathを参照し、Twitterアカウントを持つユーザーだけを返せます。
SELECT
name, profile->"$.twitter" AS `twitter`
FROM `user`
WHERE
profile->"$.twitter" IS NOT NULL;JSONデータの部分的修正
Path表記を利用してJSONを部分的に修正できるMySQL関数がいくつかあります。以下のようなものです。
- JSON_SET(doc, path, val[, path, val]...)
- JSONにデータを挿入、更新します。
- JSON_INSERT(doc, path, val[, path, val]...)
- JSONにデータを挿入します。
- JSON_REPLACE(doc, path, val[, path, val]...)
- JSONのデータを置き換えます。
- JSON_MERGE(doc, doc[, doc]...)
- 2つ以上のJSONを結合します。
- JSON_ARRAY_APPEND(doc, path, val[, path, val]...)
- 配列の最後に値を追加します。
- JSON_ARRAY_INSERT(doc, path, val[, path, val]...)
- JSONに配列を挿入します。
- JSON_REMOVE(doc, path[, path]...)
- JSONからデータを削除します。
上に示したように「プログラミング」タグを、すでに「JavaScript」タグを持つbookに追加します。
UPDATE `book`
SET tags = JSON_MERGE(tags, '["technical"]')
WHERE
JSON_SEARCH(tags, 'one', 'technical') IS NULL AND
JSON_SEARCH(tags, 'one', 'JavaScript') IS NOT NULL;もっと情報を知りたい人へ
MySQLマニュアルにはJSONデータ型や 関連したJSON関数のより詳細な情報が掲載されています。
繰り返しになりますが、JSONは絶対に必要ではない限り利用はおすすめしません。MySQLでドキュメント指向のNoSQLデータベースを模倣できるかもしれませんが、SQLの多くの利点が損なわれてしまうでしょう。本物のNoSQLシステムに切り替えた方がまだましです。しかし、JSONを使えば、SQLをベースにしたアプリケーション内の分かりにくいデータ要件に対して、多少でも手間を省けるでしょう。
（原文：How to Use JSON Data Fields in MySQL Databases）
［翻訳：中村文也／編集：Livit］
