VARCHARとNVARCHARのデータ型ミスマッチをしてもSQL Serveだとなぜ速いのか？

0. はじめに

SQL Serverなんか嫌いだ！

いきなり、本音をつぶやいてしまいましたが、SQL Serverについて受けた問い合わせをいろいろ調べているうちに得られた情報をブログに書こうと思います。（本当はもっと面白いネタがあったのですが…）

SQL Serverに限らず、よくあるSQL文の書き方のミスの１つに、データ型ミスマッチ というものがあります。例えば、以下のように、文字列型のカラムidと数値リテラルを=で比較してしまうケースが当たります

SELECT * FROM test WHERE id = 1

SQLにおいては、文字列型と数値型のデータを=で比較する場合、内部で暗黙的に文字列型のデータを数値型に変換した上で比較することになっています。
（以下のSQL文は暗黙変換のイメージです）

SELECT * FROM test WHERE to_number(id) = 1 -- Oracleの場合

こうなると、カラムidにインデックスを作成しても、このSQL文ではインデックスが利用されずに処理時間が長くなってしまいます。

SQL Severにおいて、VARCHAR型とNVARCHAR型のデータを=で比較する場合も、VARCHAR型のデータをNVARCHAR型に暗黙変換されます。そのため、遅くなるのが普通なのですが、実際には問題ないケースが存在します。

そこで、今回はVARCHAR型とNVARCHAR型のデータミスマッチをしてもインデックスが利用できるケースについて、どう動いているのかを追ってみたいと思います。

 1. インデックスが利用できるケースの再現

本記事の手順はすべてREAD COMMITED SNAPSHOTを有効にしたデータベース上で確認をとっています。

まず、以下のスクリプトでテスト用テーブルとデータ(5,000万件)を作成します。

CREATE TABLE test (
    id1    VARCHAR(10), -- クラスタ化索引あり列
    id2    VARCHAR(10), -- 索引なし列
    dummy VARCHAR(100), -- データ量かさ増し用のダミー列
    CONSTRAINT pk_test PRIMARY KEY CLUSTERED (id1)
);

-- 5,000万件のテストデータ生成
DECLARE @i INT = 0;
WHILE @i < (50000000 / 5000)
BEGIN
    -- 5,000件ずつデータ生成
    INSERT INTO test
    SELECT
        right('00000000' + cast(a.num + 5000 * @i AS VARCHAR), 8),
        right('00000000' + cast(a.num + 5000 * @i AS VARCHAR), 8),
        replicate('A', 100)
    FROM
        -- sys.all_columnsを使って5,000件データ生成
        (SELECT TOP 5000 row_number() over (order by object_id) num FROM sys.all_columns) a;
    SET @i = @i + 1;
END;

このテーブルについて、まずはVARCHAR型のデータ同士を比較する以下のSQL文を実行してみます。

SELECT
    *
FROM
    test
WHERE
    -- VARCHAR = VARCHAR
    id1 = '00000123';

すると、実行プランは以下のようにインデックスを利用できており、実際の実行も一瞬で終わります。これは想定通りの動作です。

次に、VARCHAR型とNVARCHAR型のデータを比較する以下のSQL文を実行してみます。

SELECT
    *
FROM
    test
WHERE
    -- VARCHAR = NVARCHAR
    id1 = N'00000123';

# ちなみに、このSQL文ではNVARCHAR型のリテラルを指定していますが、
# JavaプログラムからJDBCでアクセスする場合に、条件を
# PreparedStatement#setStringで渡すと、NVARCHAR型になるため、
# 似たような挙動になります。

本来は、VARCHAR型がNVARCHAR型に変換されて比較されるためインデックスが有効活用できず、フルスキャンが起こるはずですが、実際にはそうならず、インデックスを利用して一瞬で実行は完了します。以下がこのSQL文の実行プランです。

ちなみに、インデックスが作成されていないカラムid2に対して検索すると、フルスキャンが発生し、手元の環境では実行完了に90秒程度を要します。

SELECT
    *
FROM
    test
WHERE
    -- 索引がない列の検索
    id2 = '00000123';

2. 内部的な動作の確認

VARCHAR型とNVARCHAR型を比較するケースにおいて、なぜインデックスが利用でき高速なのかを探るために、詳細を見ていきたいと思います。

まずは、プラン詳細の述語の部分に注目してみます。

【述語】の部分を確認すると、CONVERT_IMPLICITが利用されており、内部では想定通りVARCHAR型のカラムid1に対してNVARCHAR型への暗黙型変換が行われていることが分かります。

ただし、インデックスを利用したデータ絞り込みの条件【シーク述語】を確認すると、SQL文の上では等価条件にも関わらず範囲選択が行われています。分かりやすく書き直すと以下のような条件ですね。

[Expr1004] < id1 AND id1 < [Expr1005]

この [Expr1004]と[Expr1005]がどこから出てきたかは、GUIではなく実行プランのXMLデータを直接見てみると何となく分かります（飽くまで何となくですが）。以下がその抜粋です。

<DefinedValue>
  <ValueVector>
    <ColumnReference Column="Expr1004" />
    <ColumnReference Column="Expr1005" />
    <ColumnReference Column="Expr1003" />
  </ValueVector>
  <ScalarOperator ScalarString="GetRangeThroughConvert([@1],[@1],(62))">

@1はSQL文で指定されたN'00000123'を示しています。
また、 GetRangeThroughConvertの第3引数(62)はVARCHAR型を表しています。

つまりは、 GetRangeThroughConvertはN'00000123'～N'00000123'というNVARCHAR型の範囲をVARCHAR型に変換した場合、どの範囲に該当するかを求めているのではないかと想像できます。

この関数はSQL Serverの内部関数なので仕様は公開されておらず、ドキュメントも見つけられませんでした。そのため、[Expr1004]と[Expr1005]が具体的にどういった値になるかは特定できませんが、それを推測するために、以下のWITH (UPDLOCK)付きのSQL文がどのキーに対してロックを取得(Lock:Acquired)するか確認します。

SELECT
    *
FROM
    test WITH (UPDLOCK)
WHERE
    -- VARCHAR = NVARCHAR
    id1 = N'00000123';

この時のSQL Profilerの取得内容は以下になります。

(ad143ec0fd9a)と(d4505477816e)という2つのキーに対してロックを取得していることが分かります。これが具体的にどのレコードを指すのかは、以下のSQL文で調べることができます。

SELECT TOP 2
    *
FROM
    test
WHERE
    %%lockres%% IN (
        '(ad143ec0fd9a)',
        '(d4505477816e)'
        );

 結果は以下のようになり、id1 = '00000123', '00000124'の2レコードであることが分かります。

また、元のSQL文の実行時ライブクエリ統計において、「読み取った行数」が2件になっていることでも、この2件の範囲をインデックスシークしていることが分かります。

つまり、id1 = N'00000123'というVARCHAR/NVARCHARの等価条件は、少なくとも、id1 <= '00000123' AND '00000124' <= id1というVARCHAR同士の範囲条件に読み替えられて、インデックスシークが利用されていると想像されます。
（<=と<の違いを考えると、実際にはもう少し広い範囲をGetRangeThroughConvertは返しているのではないかと思いますが）

以上のような動作で、VARCHAR型とNVARCHAR型のミスマッチにも関わらず、インデックスが利用可能で高速になります。

3. ならVARCHAR/NVARCHAR型のミスマッチを気にしなくて良いかというと…

インデックスシークで読み取る件数が若干増えるとはいえ高速に動作するのであれば、VARCHAR型とNVARCHAR型のミスマッチは気にしなくて良いかというと、残念ながらNGなケースがあります。

特に問題になるのが複合キーによる検索の場合です。id1とid2が複合主キーになっている以下のテーブルで見てみます。

-- id1とid2の複合主キー
CREATE TABLE test02 (
    id1   VARCHAR(10),
    id2   VARCHAR(10),
    dummy VARCHAR(100),
    CONSTRAINT pk_test02 PRIMARY KEY CLUSTERED (id1, id2)
);

このテーブルについて、以下のSQLを投げた場合、実行プランとしてはインデックスシークが選択されるのですが、実際には遅いです。

SELECT
    *
FROM
    test02
WHERE
    id1 = N'1'
AND id2 = N'00000123';

 プラン詳細を見ると【シーク述語】において、複合キーの1番目のカラムid1でしか絞り込みが行われていません。これは、「? < id1 < ? & ? < id2 < ?」という条件が指定された場合、複合インデックスが適切に利用できないケースに似ています。

また、別の問題として、先に確認したようにデータ型ミスマッチがある場合、適切にデータ型を合わせた場合に比べ、インデックスシーク範囲が広く、余分なロックが取得されるという問題もあります。

なので、やはりできる限りデータ型は一致させるべきです。

4. さいごに

今回は、SQL文でよくあるミスの1つであるデータ型ミスマッチが、SQL Serverにおいてあまり問題とならないケースとその推測される原因についてご紹介しました。加えて、問題が発生するケースについても示しました。

ここからは個人的な感想なのですが…

GetRangeThroughConvertの仕組みを知ったときは、SQL Server頑張っているなぁという第一印象を持ちました。しかし、本来はSQL文の記述の仕方を修正することで対応すべき問題であることを考えると、あまりうれしくない機能のようにも思えます。

特に、AP開発者の人が、 この仕組みのおかげでデータ型ミスマッチはいけないことだと気づくのが遅くなる、もしくは気づかないというのは、そのAP開発者にとって不幸なのではないでしょうか。

ということで、やっぱりSQL Serverは嫌いです。

（私のSQL Server嫌いは今回の件だけの話ではないのですが）

RDBMS(Oracle、SQL Server、MySQL)でJSONを扱ってみた

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 (
1,
'{
    "book_id" : 1,
    "title" : "キャズム",
    "publisher" : "翔泳社",
    "pages" : 360,
    "publish_dt" : "2014-10-04",
    "author" : [{"name" : "ジェフリー・ムーア", "role" : "著"},
                {"name" : "川又 政治", "role" : "翻訳"}]
}');

OracleではVARCHAR2、CLOB、またはBLOBのデータ型でJSONを保持します。対象の列に正しいJSONデータが入っていることを確認するために、「IS JSON」制約を利用します。

* 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 (
1,
'{
    "book_id" : 1,
    "title" : "キャズム",
    "publisher" : "翔泳社",
    "pages" : 360,
    "publish_dt" : "2014-10-04",
    "author" : [{"name" : "ジェフリー・ムーア", "role" : "著"},
                {"name" : "川又 政治", "role" : "翻訳"}]
}');

SQL Serverではnvarcahrまたはnvarchar(max)のデータ型でJSONを保持します。対象の列に正しいJSONデータが入っていることを確認するために、正しいJSONデータを受け取ったときに1を返す「ISJSON」関数を利用します。

* MySQL

 CREATE TABLE book (
    book_id int,
    book_json json,
    CONSTRAINT pk_book PRIMARY KEY (book_id)
);

 INSERT INTO book VALUES (
1,
'{
    "book_id" : 1,
    "title" : "キャズム",
    "publisher" : "翔泳社",
    "pages" : 360,
    "publish_dt" : "2014-10-04",
    "author" : [{"name" : "ジェフリー・ムーア", "role" : "著"},
                {"name" : "川又 政治", "role" : "翻訳"}]
}');

MySQLではJSONデータ型が準備されているので、JSONデータの格納にはそれを用います。個人的には一番分かり易いかなと思います。

4.2. フィールドの値(スカラー値)の取得

各JSONデータからbook_idとtitleというフィールドの値を取得します。

* Oracle 

// ドット表記法 (Oracle)
SELECT b.book_json.book_id, b.book_json.title FROM book b;

Oracleにはドット表記法という記法があり、(JSONデータを含む列名).(JSONフィールド名)で該当の値を取得することができます。とても簡単ですね。ただ、取得できる値は一律に文字列型になってしまいます。上の結果でBOOK_IDの出力結果が左寄せになっていることからも分かると思います。
ちなみに、テーブル別名を付けないとORA-00904エラーになります。

// JSON_VALUE関数の利用 (Oracle)
SELECT
    json_value(book_json, '$.book_id' RETURNING NUMBER) book_id,
    json_value(book_json, '$.title') title
FROM
    book

SQL:2016ではJSONデータからスカラー値を取得するJSON_VALUEという関数があります。Oracleの場合はRETURNING句を付けることで返るデータ型も指定することができます。出力結果でBOOK_IDが右寄せになっていることが確認できます。
標準とはいえ、正直面倒くさい表記法ですね。

// JSON_TABLE関数の利用 (Oracle)
SELECT
    x.*
FROM
    book b,
    json_table(b.book_json, '$'
        COLUMNS (book_id NUMBER PATH '$.book_id',
                 title   VARCHAR2(80 CHAR) PATH '$.title')) x

先のJSON_VALUEのSQL文では、JSON_VALUE関数が2回呼ばれていますが、実際にJSONデータ1つに対して解析も2回行われるそうです。無駄ですね。この非効率を回避するために、JSON_TABLEという関数を使うことができます。これもSQL:2016の一部です。上のSQL文の結果は先のクエリと同じです。
ただ、ぶっちゃけ分かり易い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では、Oracleのドット表記法のような記法はなく、基本SQL:2016で定義された関数を用いてJSONデータから値を取得します。
ちなみに、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;

SQL Serverでは「FOR JSON」オプションを指定することで、結果をJSON形式で返すことができます。
ただし、アプリ側が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');

SQL ServerにはJSON_TABLE関数はありません。JSONデータを表データに変換するには代わりにOPENJSON関数を利用します。ただ、結果はいわゆる縦持ちモデルで返ってくるので注意が必要です。

* MySQL

 // -> 記法 (MySQL)
SELECT
    b.book_json->'$.book_id',
    b.book_json->'$.title'
FROM
    book b

MySQLでは(JSONデータが格納された列)->(JSONパス式)という形でデータを取得することができます。Oracleのドット表記法と似ていますが、「->」の後ろがJSONパス式であることと、出力結果にダブルクォートが付くことに違いがいあります。ダブルクォートを外す関数UNQUOTEも準備されていますが少し試してみて想定通り動かなかったです（調査が不足しているのでしょう）。

// JSON_EXTRACT関数の利用 (MySQL)
SELECT
    json_extract(b.book_json, '$.book_id') book_id,
    json_extract(b.book_json, '$.title') title
FROM
    book b

MySQLにはJSON_VALUE関数はありませんが、代わりにJSON_EXTRACTという関数が準備されています（SQL:2016標準には含まれていません）。使い方はほぼ一緒ですね。

// JSON_EXTRACTは複数項目を一度に処理でき (MySQL)
SELECT
    json_extract(b.book_json, '$.book_id', '$.title')
FROM
    book b

MySQLではJSON_EXTRACT関数の1回の呼び出しで、複数のフィールドの値を取得することができます。ただし、本当に処理が効率的に行われているかは分かりません。また、結果が配列で返ってくるのは仕方ないとは言え、あまり便利ではないですね。
今回は触れませんが、MySQLにもJSON_TABLE関数は準備されています。

4.3.  フィールドの値(オブジェクト)の取得

各JSONデータからauthor(複数の著者に関しての情報を配列で保持)の1番目の値を取得します。

* Oracle

// JSON_QUERY関数の利用 (Oracle)
SELECT json_query(book_json, '$.author[0]') from book

スカラー値(数字や文字列、日付など)ではなく、JSONオブジェクトを取得する場合は、JSON_QUERY関数を使います。

* 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

JSON_QUERYはJSONオブジェクトだけではなく、配列も返すことができます。

* SQL Server

// 配列の取得(SQL Server) - うまくいかない
SELECT
    json_query([book_json], '$.author.name')
FROM
    [book];

これをSQL Serverでやる方法は分かりませんでした。JSONデータは所詮文字列なので頑張ればできるのですが、JSON関数をうまく使ってできないのでしょうか。

* MySQL

// 配列の取得 (MySQL)
SELECT
    b.book_json->'$.author[*].name'
FROM
    book b

MySQLでは[*]という表記をパス式で配列のすべての要素にアクセスできます。

4.5. 絞り込み

出版社(publisher)が「翔泳社」である書籍のタイトル(title)を取得します。

* Oracle

// JSON_EXISTS関数の利用 (Oracle)
SELECT b.book_json.title FROM book b
WHERE json_exists(b.book_json, '$?(@.publisher == "翔泳社")');

単純な絞り込みであればWHERE句でJSON_VALUEを使えばいい気もしますが(もちろん使えます)、OracleにはJSON_EXISTSという関数も用意されています。

* 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') = '翔泳社';

SQL ServerにはJSON_EXISTS関数はないので、JSON_VALUE関数を使って絞り込みを実装します。
単純なケースであれば、個人的にはこっちの方が分かり易い気もします。

* MySQL

// JSON_CONTAINSによる絞り込み (MySQL)
SELECT
    b.book_json->'$.book_id',
    b.book_json->'$.title'
FROM
    book b
WHERE
    json_contains(b.book_json, '"翔泳社"', '$.publisher')

MySQLでもJSON_EXTRACTもしくは「->」表記による絞り込みはできますが、JSON_CONTAINSという関数を使った絞り込みも可能です。ちなみにこの「CONTAINS」の意味ですが、「翔泳社という文字をpublisherに含む」という意味ではなく、「（複数存在するかもしれない）puslisherの中に、翔泳社に一致するフィールドが最低１つ含まれる」という意味です（今回は各JSONデータの中に1回しか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文では、データを集約し配列を生成するためのJSON_ARRAYAGG関数と、結果をJSON形式で返すために表データ⇒JSONデータの変換を行うJSON_OBJECT関数を利用しています。

*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

MySQLには若干表記が異なりますが、Oracleと同じJSON_OBJECTとJSON_ARRAYAGG関数が用意されています。
本当は副問い合わせなしで書きたかったのですが、どうもGROUP BYの中では「->」表記はGROUP BYの中では使えないようですね（JSON_EXTRACTも）。なぜだろう…

5. まとめ

今回、Oracle、SQL Server、MySQLのJSONの扱いを比較してみましたが、扱った範囲だと、以下のような感じでしょうか。

• OracleとMySQLは大体やりたいことができそう。あとは好みかな。
• SQL Serverは対応しているJSON関数やJSONパス式の範囲が狭く、まだこれから。

あと、今回は索引が利用できるかなどの性能面は全く見ていないので、実際に利用するにはもう少し調査が必要でしょう。

あと、今回は基本、JSONデータから表形式データを取得するケースがメインでしたが、それは準備されている関数がJSONデータ⇒SQL値の変換関数が多いことから来ています。JSONデータをそのまま処理してJSONデータとして返すにはSQL:2016の関数群(JSON_*)を使うのはちょっと違うのでしょうね。

そういうことをするには、MySQLなどではMySQL Shell for Javascript/Pythonなど使えるのでしょうか。