April 2017

Volume 32 Number 4

Cutting Edge - SQL Server 2016 で JSON データにクエリを実行する

Dino Esposito | April 2017

Dino Esposito独立した自立型システムの間でデータを移動することは、最近のほとんどのソフトウェアが行っていることです。そして、そのデータ転送を支えるユビキタス言語が JSON です。JSON は JavaScript Object Notation の略で、オブジェクトの状態をレイアウトするテキストベースの方法です。JSON により、オブジェクト状態のシリアル化が容易になり、あるシステムから別のシステムへネットワークを介して簡単に転送できるようになります。JSON は、特に異種混合システムで有効です。

こうした特徴から、JSON は XML がなり損ねた Web の共通言語になりました。個人的には、JSON が XML よりも読みやすいという意見にはそれほど同調しません。とは言え、JSON は XML よりもずっとコンパクトかつ軽量のテキスト形式です。人手による編集が可能です。コンピュータの観点では、多数のソフトウェア プラットフォームやハードウェア プラットフォームがこれを簡単に解析して理解します。

JSON 文字列はプレーンなテキスト文字列です。SQL Server など、任意のバージョンのリレーショナル データベース管理システム (RDBMS) は、RDBMS のコンテンツ レイアウトとは関係なく、その文字列を格納できるようになります。ただし SQL Server 2016 は、Microsoft データベースのバージョンとして初めて、既存のテーブル形式のデータを JSON として読み取ること、テーブル形式のデータを JSON として保存すること、さらに重要なことに、JSON コンテンツが個別の列で構成される実際のコレクションであるかのように、JSON 文字列内でクエリを実行することを可能にしています。

SQL Server 2016 の JSON 関数に関する、体系化された包括的な概要については、MSDN ドキュメント (bit.ly/2llab1n、英語) を参照してください。また、Simple Talk の記事 (bit.ly/26rprwv、英語) でも、SQL Server 2016 の JSON についての優れた要約を確認できます。今回は、SQL Server 2016 での JSON についてのビジネス指向の考え方と、リレーショナルな永続化層で JSON データを利用することに対するシナリオベースの視点を取り上げます。

永続化層における JSON データ

JSON の目的を理解するうえで重要な言葉は「転送」と「シリアル化」の 2 つです。つまり、JSON とはソフトウェア エンティティの状態をレイアウトする形式で、複数のプロセス空間がその状態を転送できるようにし、転送元と転送先の両方がその状態を正しく確実に理解できる形式と言えます。すばらしいことですが、今回は SQL Server における JSON、つまり永続化層における JSON についてのコラムです。そこで、まずは基本的な疑問から始めることにします。 SQL Server のデータを JSON として保存することになるのはどのような場合でしょう。

リレーショナル データベース テーブルは固定数の列で構成され、可変長または固定長の文字列型、日付型、数値型、ブール型など、各列が独自のデータ型を持ちます。JSON はネイティブ データ型ではありません。JSON データを含む SQL Server 列は、データベースから見れば、プレーンな文字列型の列です。JSON データは、標準の文字列を書き込む場合と同様、テーブル列に書き込むことができます。このような操作は任意のバージョンの SQL Server だけでなく、他の RDBMS でも可能です。

では、どのような場合に JSON 文字列を取得し、最終的にデータベースに格納することになるのでしょう。 主なシナリオは 2 つです。 1 つは、こうした文字列を、Web サービスや、データを転送するなんらかの形式の外部エンドポイント (ネットワーク接続型のデバイスやセンサーなど) から取得するシナリオです。もう 1 つは、関連性のある情報をグループにして、単一のデータ項目として表示する場合に JSON データが便利な方法になるようなシナリオです。こうした状況は、通常、半構造化されたデータを扱う場合に発生します。たとえば、イベント ソーシングのシナリオで保存対象となるビジネス イベントを表すデータです。もっとわかりやすく言えば、金融、貿易、評価、監視、工業オートメーションや制御といった分野向けのリアルタイム システムなど、本質的にイベント ドリブン型のビジネス コンテキスト分野において、保存対象となるビジネス イベントを表すデータです。このような場合はすべて、ストレージが構造化された形式に正規化されている可能性があります。そのため、リレーショナル テーブルの文字列型の列に収まるように、関連情報を可変長の単一データ項目の形式でシリアル化します。

前述のように、永続化する JSON コンテンツは外部ソースから取得される可能性があります。または、次のように C# オブジェクトのインスタンスからシリアル化によって生成されることもあります。

foreach (var c in countries)
{
  // Serialize the C# object to JSON
  var json = JsonConvert.SerializeObject(c);
  // Save content to the database
  record.JsonColumn = json;
}

また、Entity Framework (EF) を使用して JSON データをデータベース テーブルのいずれかの列に保存することもあります。

SQL Server 2016 は、こうした状況をさらに 1 段階進め、JSON データをテーブル行に変換できるようにします。この機能により、コードでの多くの手間と CPU サイクルが節約される可能性があります。それは、JSON テキストに手を加えずにそのままデータベースに送り込むことができるようになるためです。その際、最初にアプリケーション コードでJSON テキストそのものを解析して C# オブジェクトに変換してから、EF または ADO.NET の直接呼び出しにパススルーするという手順が必要なくなります。この目標実現の鍵を握るのが、次に示す新しい OPENJSON 関数です。

declare @country nvarchar(max) = '{
  "id" : 101,
  "name": "United States",
  "continent": "North America"
}';
  INSERT INTO Countries
    SELECT * FROM OPENJSON(@country)
    WITH (id int,
      name nvarchar(100),
      continent nvarchar(100))

この関数を使用して、プレーンな JSON テキストから標準のテーブル行への挿入や更新が可能になります。WITH 句により、JSON プロパティを既存のテーブル列にマップできるようになります。

イベント ソーシングのシナリオ

2016 年 12 月号のコラムで、アプリケーションの過去の状態を保存する新たなパターンとして、イベント ソーシングを取り上げました (msdn.com/magazine/mt790196)。イベント ソーシングでは、最新かつ既知の適切な状態を保存するのではなく、状態を変更する個々のビジネス イベントをすべて保存し、過去のイベントを再生することで最新状態を再構築します。

イベント ソーシングの実装の最も重要な側面は、過去のイベントを効果的に保存し、取得できることです。イベントは、その種類と利用可能な情報に応じてすべて異なり、スキーマも異なる可能性があります。同時に、イベントの種類ごとに個別の (リレーショナル) ストアがあるとそれも問題になります。イベントが非同期に発生し、さまざまなエンティティと、状態の異なるセグメントに影響する可能性があるためです。これらを異なるテーブルで管理すると、状態を再構築する際に、テーブルどうしの JOIN が必要になり、コストが高くなる可能性があります。したがって、イベントをオブジェクトとして保存するのが最も推奨されるオプションです。NoSQL ストアでは、この作業が非常に適切に実行されます。では、リレーショナル データベースを代わりに使用してイベント ソーシングを行うことはできるでしょうか。

イベントを JSON として保存するのは、どのバージョンの SQL Server でも実現可能なオプションですが、大量のイベントがストア内にある場合、JSON を効率的に読み取ることができない恐れがあります。ただし、SQL Server 2016 のネイティブ JSON 機能があればこの状況は一変し、イベント ソーシング シナリオに SQL Server を使用することが現実的になります。しかし、データベース テーブルからの JSON にどのようにクエリを実行するのでしょう。

JSON コンテンツのデータへのクエリ

それでは、標準のリレーショナル テーブルにどうにか JSON データの列を 1 つ以上用意できたとします。つまり、プリミティブ データ型の列と、JSON データを設定した列が共存する状態です。SQL Server 2016 の新機能を使わなければ、JSON 列はプレーンなテキスト フィールドとして扱われ、T-SQL 文字列や、LIKE、SUBSTRING、TRIM などのテキスト命令を使用しなければクエリできません。今回のデモの目的に、少数の表形式の列を含む Countries という列と、残りのレコードをすべて JSON としてシリアル化して格納した Serialized という列を作成しました (図 1 参照)。

JSON 列を含む国に関するサンプル データベース
図 1 JSON 列を含む国に関するサンプル データベース

サンプル テーブルでシリアル化された JSON オブジェクトは次のようなものです。

{
  "CountryCode":"AD",
  "CountryName":"Andorra",
  "CurrencyCode":"EUR",
  "Population":"84000",
  "Capital":"Andorra la Vella",
  "ContinentName":"Europe",
  "Continent":"EU",
  "AreaInSqKm":"468.0",
  "Languages":"ca",
  "GeonameId":"3041565",
  "Cargo":null

次の T-SQL クエリは、総人口が 1 億人を超える国のみを選択する方法を示しています。このクエリには標準のテーブル列と JSON プロパティが混在しています。

SELECT CountryCode,
  CountryName,
  JSON_VALUE(Serialized, '$.Population') AS People
FROM Countries
WHERE ISJSON(Serialized) > 0 AND
  JSON_VALUE(Serialized, '$.Population') > 100000000
ORDER BY JSON_VALUE(Serialized, '$.AreaInSqKm')

JSON_VALUE 関数は、JSON 列の名前 (または、JSON 文字列に設定されたローカル変数) を受け取り、指定されたパスに従ってスカラー値を抽出します。図 2 に示すように、$ 記号はシリアル化された JSON オブジェクトのルートを表します。

JSON クエリの結果
図 2 JSON クエリの結果

JSON 列はプレーンな NVARCHAR 列として構成されているため、列のコンテンツが実際の JSON かどうかをチェックするには ISJSON 関数を使用します。コンテンツが JSON であれば、この関数は正の値を返します。

JSON_VALUE は、選択しているプロパティとは無関係に、常に 4,000 バイト以下の文字列を返します。もっと長い戻り値を想定する場合は、代わりに OPENJSON を使用します。どちらにしても、適切な型の値を取得するには、CAST を検討します。前の例に戻ります。国の人口の書式をコンマ区切りにするとします (一般的には、あまりよい考えではありません。プレゼンテーション層でデータの書式を設定する方がコードの柔軟性がはるかに高くなります)。 SQL FORMAT 関数は数値を受け取ると想定しているため、JSON 値を直接渡すとエラーが発生します。機能させるには、明示的な CAST を利用する必要があります。

SELECT CountryCode,
  CountryName,
  FORMAT(CAST(
    JSON_VALUE(Serialized, '$.Population') AS int), 'N0')
    AS People
FROM Countries
WHERE ISJSON(Serialized) > 0 AND
  JSON_VALUE(Serialized,'$.Population') > 100000000
ORDER BY JSON_VALUE(Serialized, '$.AreaInSqKm')

JSON_VALUE は、単一のスカラー値しか返すことはできません。抽出する配列が、入れ子になったオブジェクトの配列の場合、JSON_QUERY 関数を使用する必要があります。

JSON データに対してクエリを実行することはどの程度効果的なのでしょう。 いくつかテストしてみましょう。

SQL Server 2016 における JSON コンテンツのインデックス作成

当たり前のように感じられるかもしれませんが、データベースの JSON 文字列全体をクエリしてから、Newtonsoft JSON などの専用ライブラリを使ってその文字列をメモリ内で解析することは、必ず機能するとはいえ、すべてのケースで効率的なアプローチになるわけではありません。データベース内のレコード数と、必要なデータを必要な形式で取得するのに実際どれだけの時間が掛かるかによって、効率は大きく変わります。おそらく、アプリケーションで随時実行するクエリの場合は、JSON データをメモリ内で処理することも、依然として選択肢の 1 つになるでしょう。ただし、一般的に、JSON 専用関数を使用してクエリを実行し、SQL Server による内部での解析を可能にする方が、コードは若干速くなります。JSON データにインデックスを追加すれば、その速度差はさらに大きくなります。

ただし、JSON 列にインデックスを作成する必要はありません。この方法では、JSON 値が単一の文字列としてインデックス付けされるためです。JSON 文字列全体や、そのサブセットをクエリするのは難しくなります。もっと現実的なのは、シリアル化された JSON オブジェクトの特定のプロパティの値をクエリすることです。もっと効率の高いアプローチは、1 つ以上の JSON プロパティに基づく計算列を 1 つ以上作成して、次にそれらの列にインデックスを設定する方法です。T-SQL の例を次に示します。

-- Add a computed column
ALTER TABLE dbo.Countries
ADD JsonPopulation
AS JSON_VALUE(Serialized, '$.Population')
-- Create an index
CREATE INDEX IX_Countries_JsonPopulation
ON dbo.Countries(JsonPopulation)

ここでも、JSON_VALUE から NVARCHAR が返されています。そのため、CAST を追加しない限り、インデックスはテキストに対して作成されます。

興味深いことに、JSON の解析は、XML 型や Spatial 型などの一部の特殊な型をシリアル化解除するよりも高速です。詳細については、bit.ly/2kthrrC (英語) を参照してください。つまり、少なくとも JSON の解析は他の型のプロパティを取得するよりも高速です。

JSON と EF

一般的には、SQL Server 2016 の JSON サポートは主に T-SQL 構文を使って公開されており、ツールが非常に限られているのが現状です。特に EF の場合は、EF6 の SqlQuery メソッドと、EF Core の FromSql を除き、JSON データに対してクエリを実行する機能は現在用意されていません。ですが、これは C# クラスの複雑なプロパティ (たとえば配列) を JSON 列にシリアル化できないということではありません。EF Core の優れたチュートリアルについては、bit.ly/2kVEsam (英語) を参照してください。

まとめ

SQL Server 2016 ではネイティブ JSON 機能がいくつか導入されます。そのため、保存された JSON データを標準の行セットとして効率よくクエリできます。クエリの効率が上がるのは主として、JSON データが半構造化されたデータの集まりをシリアル化している場合です。1 つ以上の JSON プロパティの値が反映される計算列からインデックスを作成すると、パフォーマンスが確実に向上します。

JSON データはプレーンなテキストとして保存され、XML や Spatial のような特殊な型と見なされることはありません。ですから、JSON 列をどの SQL Server オブジェクトでもすぐに使用できます。同じことは、依然として人気のある XML、CLR、Spatial などの他の複雑な型には当てはまりません。

今回は、JSON から行セットに変換するシナリオを中心に取り上げました。ただし SQL Server 2016 では、標準の T-SQL クエリを記述し、FOR JSON 句を利用して結果を JSON オブジェクトにマップしている場合、行セットから JSON に対するクエリ シナリオも完全にサポートされます。詳細については、bit.ly/2fTKly7 (英語) を参照してください。


Dino Esposito は、『Microsoft .NET: Architecting Applications for the Enterprise』(Microsoft Press、2014 年) および『Modern Web Applications』(Microsoft Press、2016 年) の著者です。JetBrains の .NET および Android プラットフォームのテクニカル エバンジェリストでもあります。世界各国で開催される業界のイベントで頻繁に講演しており、software2cents.wordpress.com (英語) や Twitter (@despos、英語) でソフトウェアに関するビジョンを紹介しています。

この記事のレビューに協力してくれたマイクロソフト技術スタッフの Jovan Popovic に心より感謝いたします。