1. はじめに
複雑な構造のデータやデータ定義が頻繁に変わるデータを容易に格納できるという点で、NoSQLの中でもMongoDBやCouchbase、MarklogicなどのJSON形式でデータを保持できるドキュメント指向DBの利用は広がりつつあります。ただし、JSON形式のデータを保持できるというだけであれば、昨今のメジャーなRDBMSにおいても機能的には可能になってきています。また、SQLにおけるJSONの扱いはSQL:2016という標準で規定されているため、各製品で利用できる関数や記法などはある程度似ています。ただ、実際には標準への準拠状況や製品独自の表記/オプションにおいて少なくない差異がある状況になっています。
そこで今回(?)は、メジャーなRDBMSの内でもOracle、SQL Server、MySQLの３DBにおける基本的なJSONの取り扱い方を簡単に見ていきたいと思います。
# PostgreSQLも見ようと思ったのですが、力尽きた…
マニュアルなどは当たっていますが、隅々まで読み込んだわけではないので、間違いやより良い方法があるかもしれません。その点はご容赦ください。また、最初に調べ始めたのがOracleのため、若干Oracle基準の説明になっている部分がありますが、ご承知おきください。
2. 動作確認環境
- Oracle Database 18c (18.3.0) for Linux x86-64 (Enterprise Edition)
- SQL Server 2017 for Linux (Evaluation Edition)
- MySQL 8.0.12 for Linux x86-64 (Community Edition)
3. 使用データ
今回の動作確認では以下のデータを使っていきます。{
"book_id" : 1,
"title" : "キャズム",
"publisher" : "翔泳社",
"pages" : 360,
"publish_dt" : "2014-10-04",
"author" : [{"name" : "ジェフリー・ムーア", "role" : "著"},
{"name" : "川又 政治", "role" : "翻訳"}]
}
{
"book_id" : 2,
"title" : "ビジョナリー・カンパニー",
"publisher" : "日経BP社",
"pages" : 475,
"publish_dt" : "1995-09-26",
"author" : [{"name" : "ジム・コリンズ", "role" : "著"},
{"name" : "山岡 洋一", "role" : "翻訳"}]
}
{
"book_id" : 3,
"title" : "世界の経営学者はいま何を考えているのか",
"publisher" : "英治出版",
"pages" : 352,
"publish_dt" : "2012-11-13",
"author" : [{"name" : "入山 章栄", "role" : "著"}]
}
{
"book_id" : 4,
"title" : "イノベーションへの解",
"publisher" : "翔泳社",
"pages" : 373,
"publish_dt" : "2003-12-13",
"author" : [{"name" : "クレイトン・クリステンセン", "role" : "著"},
{"name" : "マイケル・レイナー", "role" : "著"},
{"name" : "玉田 俊平太", "role" : "監修"},
{"name" : "山岡 洋一", "role" : "翻訳"}]
}
{
"book_id" : 5,
"title" : "機械との競争",
"publisher" : "日経BP社",
"pages" : 176,
"publish_dt" : "2013-02-07",
"author" : [{"name" : "エリック・ブリニョルフソン", "role" : "著"},
{"name" : "アンドリュー・マカフィー", "role" : "著"},
{"name" : "村井 章子", "role" : "翻訳"}]
}
4. 動作確認
4.1. データ挿入
JSONデータを格納するテーブルを作成し、データを挿入します。* Oracle
CREATE TABLE book (
book_id NUMBER(3),
book_json VARCHAR2(4000),
CONSTRAINT pk_book PRIMARY KEY (book_id),
CONSTRAINT c_book_01 CHECK (book_json IS JSON)
);
INSERT INTO book VALUES (OracleではVARCHAR2、CLOB、またはBLOBのデータ型でJSONを保持します。対象の列に正しいJSONデータが入っていることを確認するために、「IS JSON」制約を利用します。
1,
'{
"book_id" : 1,
"title" : "キャズム",
"publisher" : "翔泳社",
"pages" : 360,
"publish_dt" : "2014-10-04",
"author" : [{"name" : "ジェフリー・ムーア", "role" : "著"},
{"name" : "川又 政治", "role" : "翻訳"}]
}');
* SQL Server
CREATE TABLE [book] (
[book_id] INT,
[book_json] nvarchar(4000),
CONSTRAINT [pk_book] PRIMARY KEY ([book_id]),
CONSTRAINT [c_book_01] CHECK (ISJSON([book_json]) = 1)
);
INSERT INTO [book] VALUES (SQL Serverではnvarcahrまたはnvarchar(max)のデータ型でJSONを保持します。対象の列に正しいJSONデータが入っていることを確認するために、正しいJSONデータを受け取ったときに1を返す「ISJSON」関数を利用します。
1,
'{
"book_id" : 1,
"title" : "キャズム",
"publisher" : "翔泳社",
"pages" : 360,
"publish_dt" : "2014-10-04",
"author" : [{"name" : "ジェフリー・ムーア", "role" : "著"},
{"name" : "川又 政治", "role" : "翻訳"}]
}');
* MySQL
CREATE TABLE book (
book_id int,
book_json json,
CONSTRAINT pk_book PRIMARY KEY (book_id)
);
INSERT INTO book VALUES (MySQLではJSONデータ型が準備されているので、JSONデータの格納にはそれを用います。個人的には一番分かり易いかなと思います。
1,
'{
"book_id" : 1,
"title" : "キャズム",
"publisher" : "翔泳社",
"pages" : 360,
"publish_dt" : "2014-10-04",
"author" : [{"name" : "ジェフリー・ムーア", "role" : "著"},
{"name" : "川又 政治", "role" : "翻訳"}]
}');
4.2. フィールドの値(スカラー値)の取得
各JSONデータからbook_idとtitleというフィールドの値を取得します。* Oracle
// ドット表記法 (Oracle)
SELECT b.book_json.book_id, b.book_json.title FROM book b;
ちなみに、テーブル別名を付けないとORA-00904エラーになります。
// JSON_VALUE関数の利用 (Oracle)
SELECT
json_value(book_json, '$.book_id' RETURNING NUMBER) book_id,
json_value(book_json, '$.title') title
FROM
book
標準とはいえ、正直面倒くさい表記法ですね。
// JSON_TABLE関数の利用 (Oracle)先のJSON_VALUEのSQL文では、JSON_VALUE関数が2回呼ばれていますが、実際にJSONデータ1つに対して解析も2回行われるそうです。無駄ですね。この非効率を回避するために、JSON_TABLEという関数を使うことができます。これもSQL:2016の一部です。上のSQL文の結果は先のクエリと同じです。
SELECT
x.*
FROM
book b,
json_table(b.book_json, '$'
COLUMNS (book_id NUMBER PATH '$.book_id',
title VARCHAR2(80 CHAR) PATH '$.title')) x
ただ、ぶっちゃけ分かり易いSQL文かというと…
* SQL Server
// JSON_VALUE関数の利用 (SQL Server)
SELECT
json_value([book_json], '$.book_id') [book_id],
json_value([book_json], '$.title') [title]
FROM
[book];
ちなみに、SQL ServerのJSON_VALUE関数には、OracleのRETURNINGのようなデータ型を指定するオプションはありません。
// 結果をJSON形式で返す (SQL Server)
SELECT
json_value([book_json], '$.book_id') [book_id],
json_value([book_json], '$.title') [title]
FROM
[book]
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;
ただし、アプリ側がJSON形式でデータを欲しいことは多々ありますが、複数のJSONデータをカンマでつなぎ1行という形式が使いやすいかというと、どうなんでしょうね。
// OPENJSON関数の利用 (SQL Server)
SELECT
[b].[book_id],
[x].[key],
[x].[key],
[x].[value]
FROM
[book] [b]
CROSS APPLY
openjson([b].[book_json]) [x]
WHERE
[x].[key] IN ('title', 'pages');
* MySQL
// -> 記法 (MySQL)
SELECT
b.book_json->'$.book_id',
b.book_json->'$.title'
FROM
book b
// JSON_EXTRACT関数の利用 (MySQL)
SELECT
json_extract(b.book_json, '$.book_id') book_id,
json_extract(b.book_json, '$.title') title
FROM
book b
// JSON_EXTRACTは複数項目を一度に処理でき (MySQL)
SELECT
json_extract(b.book_json, '$.book_id', '$.title')
FROM
book b
今回は触れませんが、MySQLにもJSON_TABLE関数は準備されています。
4.3. フィールドの値(オブジェクト)の取得
各JSONデータからauthor(複数の著者に関しての情報を配列で保持)の1番目の値を取得します。* Oracle
// JSON_QUERY関数の利用 (Oracle)
SELECT json_query(book_json, '$.author[0]') from book
* SQL Server
// JSON_QUERY関数の利用 (SQL Server)
SELECT
json_query([book_json], '$.author[0]')
FROM
[book];
JSON_QUERYの使い方は、Oracleとほぼ一緒です。
* MySQL
// JSON_EXTRACTによるJSONオブジェクトの取得(MySQL)SELECT
b.book_json->'$.author[0]'
FROM
book b
Oracle/SQLServerの場合、取得対象がスカラー値かJSONオブジェクト/配列かでJSON_VALUEとJSON_QUERYを使い分ける必要がありますが、MySQLの場合はどちらでもJSON_EXTRACTもしくは「->」表記を使えます。メリデメあって良し悪しは一概には言えませんが。
4.4. 配列からのデータ取得
著者名のリストを取得します。* Oracle
// 配列の取得 (Oracle)
SELECT json_query(book_json, '$.author.name' WITH WRAPPER) from book
* SQL Server
// 配列の取得(SQL Server) - うまくいかないこれをSQL Serverでやる方法は分かりませんでした。JSONデータは所詮文字列なので頑張ればできるのですが、JSON関数をうまく使ってできないのでしょうか。
SELECT
json_query([book_json], '$.author.name')
FROM
[book];
* MySQL
// 配列の取得 (MySQL)
SELECT
b.book_json->'$.author[*].name'
FROM
book b
4.5. 絞り込み
出版社(publisher)が「翔泳社」である書籍のタイトル(title)を取得します。* Oracle
// JSON_EXISTS関数の利用 (Oracle)
SELECT b.book_json.title FROM book b
WHERE json_exists(b.book_json, '$?(@.publisher == "翔泳社")');
* SQL Server
// JSON_VALUE関数による絞り込み (SQL Server)
SELECT
json_value([book_json], '$.book_id') [book_id],
json_value([book_json], '$.title') [title]
FROM
[book]
WHERE
json_value([book_json], '$.publisher') = '翔泳社';
単純なケースであれば、個人的にはこっちの方が分かり易い気もします。
* MySQL
// JSON_CONTAINSによる絞り込み (MySQL)
SELECT
b.book_json->'$.book_id',
b.book_json->'$.title'
FROM
book b
WHERE
json_contains(b.book_json, '"翔泳社"', '$.publisher')
4.5. 集約(配列の作成)
出版社(publisher)ごとに書籍のタイトルを集約し、配列で返します。* Oracle
// JSON_OJECT関数とJSON_ARRAYAGG関数の利用 (Oracle)
SELECT
json_object(
'publisher' VALUE x.publisher,
'titles' VALUE json_arrayagg(x.title))
FROM
book b,
json_table(b.book_json, '$'
COLUMNS (publisher VARCHAR2(80 CHAR) PATH '$.publisher',
title VARCHAR2(80 CHAR) PATH '$.title')) x
GROUP BY
x.publisher;
*SQL Server
SQL Serverでこれを実現する方法は分かりませんでした。もちろん文字列処理をすれば可能なのは分かりますが。* MySQL
// JSON_OBJECTとJSON_ARRAYAGGを利用 (MySQL)
SELECT
json_object('publisher', x.publisher, 'titles', json_arrayagg(x.title))
FROM
(
SELECT
b.book_json->'$.publisher' publisher,
b.book_json->'$.title' title
FROM
book b
) x
GROUP BY
x.publisher
本当は副問い合わせなしで書きたかったのですが、どうもGROUP BYの中では「->」表記はGROUP BYの中では使えないようですね（JSON_EXTRACTも）。なぜだろう…
5. まとめ
今回、Oracle、SQL Server、MySQLのJSONの扱いを比較してみましたが、扱った範囲だと、以下のような感じでしょうか。- OracleとMySQLは大体やりたいことができそう。あとは好みかな。
- SQL Serverは対応しているJSON関数やJSONパス式の範囲が狭く、まだこれから。
そういうことをするには、MySQLなどではMySQL Shell for Javascript/Pythonなど使えるのでしょうか。
