次の方法で共有

JSON ドキュメントの格納

対象者:SQL Server 2016 (13.x) およびそれ以降のバージョン Azure SQL DatabaseAzure SQL Managed InstanceSQL Database in Microsoft Fabric

SQL データベース エンジンは、ネイティブ JSON 機能を提供します。標準 SQL 言語を利用し、JSON ドキュメントを解析できます。 JSON ドキュメントを SQL Database エンジンに格納し、NoSQL データベースと同様に JSON データにクエリを実行できます。 この記事では、JSON ドキュメントを保存するためのオプションについて説明します。

JSON ストレージの形式

最初のストレージ設計では、JSON ドキュメントをテーブルに格納する方法を決定します。 次の 2 つのオプションを使用できます。

  • LOB ストレージ - JSON ドキュメントは、データ型 json または nvarchar を持つ列に as-is 格納できます。 この方法では、読み込み速度が文字列型の列の読み込みに匹敵するため、データの読み込みと取り込みをすばやく行うのに最適です。 この方法では、クエリの実行中に未加工の JSON ドキュメントを解析する必要があるため、JSON 値のインデックス作成が実行されていない場合は、クエリ/分析時間のパフォーマンスがさらに低下する可能性があります。
  • リレーショナル ストレージ - JSON ドキュメントは、 OPENJSON、 JSON_VALUE 、または JSON_QUERY 関数を使用してテーブルに挿入されている間に解析できます。 入力 JSON ドキュメントのフラグメントは、データ型 json または nvarchar を持つ JSON サブ要素を含む列に格納できます。 この方法では、読み込み中に JSON の解析が行われるため読み込み時間が長くなります。ただし、クエリは、リレーショナル データに対する従来のクエリのパフォーマンスに匹敵します。
  • 現在 SQL Server では、JSON はビルトインのデータ型ではありません。

注

JSON データ型:

  • は、SQL Server 2025 または Always-up-to-date更新ポリシーを使用して、Azure SQL Database と Azure SQL Managed Instance で一般提供されています。
  • SQL Server 2025 (17.x) および Fabric の SQL データベースはプレビュー段階にあります。

クラシック テーブル

SQL Server または Azure SQL データベースに JSON ドキュメントを保存する最も簡単な方法は、ドキュメントの ID とドキュメントのコンテンツが含まれる 2 列のテーブルを作成することです。 例えば次が挙げられます。

create table WebSite.Logs (
    [_id] bigint primary key identity,
    [log] nvarchar(max)
);

または、サポートされている場合:

create table WebSite.Logs (
    [_id] bigint primary key identity,
    [log] json
);

この構造は、クラシック ドキュメント データベースで見られるコレクションと同じです。 プライマリ キー _id は自動的に増分される値であり、あらゆるドキュメントに一意の ID を与え、短時間の検索を可能にします。 ID でドキュメントを検索したり、保存されているドキュメントを ID 別に更新したりする、従来の NoSQL シナリオには、この構造が最適です。

  • JSON ドキュメントを格納できる場合は、ネイティブの json データ型を使用します。
  • nvarchar(max) データ型を使用すると、最大 2 GB のサイズの JSON ドキュメントを格納できます。 ただし、JSON ドキュメントが 8 KB を超えない場合は、パフォーマンス上の理由から nvarchar(max) の代わりに nvarchar(4000) を使用することをお勧めします。

先の例で作成したサンプル テーブルでは、有効な JSON ドキュメントが log 列に保存されているものと想定しています。 有効な JSON が log 列に間違いなく保存されている場合、その列で CHECK 制約を追加できます。 例えば次が挙げられます。

ALTER TABLE WebSite.Logs
    ADD CONSTRAINT [Log record should be formatted as JSON]
                   CHECK (ISJSON([log])=1)

誰かがテーブルにドキュメントを挿入したり、テーブルのドキュメントを更新したりするたびに、JSON ドキュメントが正しく書式設定されていることがこの制約により検証されます。 制約がないとき、テーブルは挿入のために最適化されます。JSON ドキュメントは何の処理もなく列に直接追加されるためです。

テーブルに JSON ドキュメントを保存するとき、標準の Transact-SQL 言語を使用し、ドキュメントにクエリを実行できます。 例えば次が挙げられます。

SELECT TOP 100 JSON_VALUE([log], '$.severity'), AVG( CAST( JSON_VALUE([log],'$.duration') as float))
 FROM WebSite.Logs
 WHERE CAST( JSON_VALUE([log],'$.date') as datetime) > @datetime
 GROUP BY JSON_VALUE([log], '$.severity')
 HAVING AVG( CAST( JSON_VALUE([log],'$.duration') as float) ) > 100
 ORDER BY AVG( CAST( JSON_VALUE([log],'$.duration') as float) ) DESC

これは、 任意 の T-SQL 関数とクエリ句を使用して JSON ドキュメントにクエリを実行できる強力な利点です。 SQL Server と SQL Database のクエリには、JSON ドキュメントの分析に利用できる制約がありません。 JSON_VALUE 関数で JSON ドキュメントから値を抽出し、それを他の値と同様にクエリで利用できます。

このように豊富な T-SQL クエリ構文を使用できることが、SQL Server および SQL Database と従来の NoSQL データベースの大きな違いです。Transact-SQL では、ほとんどの場合、JSON データの処理に必要なすべての関数を使用できます。

Indexes

クエリを実行する際、一部のプロパティでドキュメントが検索されることがよくある場合 (JSON ドキュメントの severity プロパティなど)、そのプロパティに行ストアの非クラスター化インデックスを追加し、クエリを速めることができます。

指定のパスで (つまり、パス $.severity で) JSON 列から JSON 値を公開する計算列を作成し、この計算列で標準インデックスを作成できます。 例えば次が挙げられます。

create table WebSite.Logs (
    [_id] bigint primary key identity,
    [log] nvarchar(max),
    [severity] AS JSON_VALUE([log], '$.severity'),
    index ix_severity (severity)
);

この例で使用されている計算列は永続ではない列または仮想の列であり、テーブルにスペースを追加しません。 インデックス ix_severity で使用され、次の例のようなクエリのパフォーマンスを改善します。

SELECT [log]
FROM Website.Logs
WHERE JSON_VALUE([log], '$.severity') = 'P4'

このインデックスの重要な特性の 1 つは、それが照合順序対応であることです。 元の nvarchar 列に COLLATION プロパティ (大文字と小文字の区別や日本語など) がある場合、インデックスは 、nvarchar 列に関連付けられている言語規則または大文字と小文字の区別規則に従って編成されます。 JSON ドキュメントの処理時にカスタムの言語ルールを使用する必要があるアプリケーションを世界市場向けに開発する場合、この照合順序対応は重要な機能かもしれません。

大きなテーブルと列ストア形式

コレクションに大量の JSON ドキュメントが予想される場合、コレクションにクラスター化列ストア インデックスを追加することをお勧めします。次の例をご覧ください。

create sequence WebSite.LogID as bigint;
go
create table WebSite.Logs (
    [_id] bigint default(next value for WebSite.LogID),
    [log] nvarchar(max),
    INDEX cci CLUSTERED COLUMNSTORE
);

クラスター化列ストア インデックスはデータの圧縮率が高く (最大 25x)、ストレージの領域要件を大幅に減らし、ストレージ コストを下げ、ワークロードの I/O パフォーマンスを増やします。 また、クラスター化列ストア インデックスは JSON ドキュメントのテーブル スキャンと分析のために最適化されるため、この種のインデックスはログ分析には最適な選択肢かもしれません。

先の例ではシーケンス オブジェクトを利用し、値を _id 列に割り当てています。 ID 列では、シーケンスと ID はいずれも有効なオプションです。

頻繁に変更されるドキュメントとメモリ最適化テーブル

コレクションで多数の更新、挿入、削除操作が予想される場合、メモリ最適化テーブルに JSON ドキュメントを保存できます。 メモリ最適化 JSON コレクションでは、データが常にメモリ内に保持されます。そのため、ストレージの I/O オーバーヘッドがありません。 また、メモリ最適化 JSON コレクションにはロック制御がまったくありません。つまり、ドキュメントを操作したとき、他の操作がブロックされることはありません。

クラシック コレクションをメモリ最適化コレクションに変換しなければならない場合は、次の例に示すように、テーブル定義の後に WITH (MEMORY_OPTIMIZED=ON) オプションを指定します。 これで JSON コレクションがメモリ最適化されます。

CREATE TABLE WebSite.Logs (
  [_id] bigint IDENTITY PRIMARY KEY NONCLUSTERED,
  [log] nvarchar(max)
) WITH (MEMORY_OPTIMIZED=ON)

メモリ最適化テーブルは、頻繁に変更するドキュメントに最適な選択肢です。 メモリ最適化テーブルの利用を検討するとき、パフォーマンスについても考慮してください。 可能であれば、パフォーマンスが大幅に向上する可能性があるため、メモリ最適化コレクション内の JSON ドキュメントに nvarchar(max) の代わりに nvarchar(4000) データ型を使用します。 json データ型は、メモリ最適化テーブルではサポートされていません。

クラシック テーブルの場合と同様に、計算列を使用して、メモリ最適化テーブルで公開するフィールドでインデックスを追加できます。 例えば次が挙げられます。

CREATE TABLE WebSite.Logs (

  [_id] bigint IDENTITY PRIMARY KEY NONCLUSTERED,
  [log] nvarchar(max),

  [severity] AS cast(JSON_VALUE([log], '$.severity') as tinyint) persisted,
  INDEX ix_severity (severity)

) WITH (MEMORY_OPTIMIZED=ON)

パフォーマンスを最大化するために、プロパティの値を保持できるよう、JSON 値をできるだけ小さい型にキャスト変換します。 前の例では、 tinyint が使用されています。

また、ストアド プロシージャに JSON ドキュメントを更新する SQL クエリを置き、ネイティブ コンパイルの利点を活かすことができます。 例えば次が挙げられます。

CREATE PROCEDURE WebSite.UpdateData(@Id int, @Property nvarchar(100), @Value nvarchar(100))
WITH SCHEMABINDING, NATIVE_COMPILATION

AS BEGIN
    ATOMIC WITH (transaction isolation level = snapshot,  language = N'English')

    UPDATE WebSite.Logs
    SET [log] = JSON_MODIFY([log], @Property, @Value)
    WHERE _id = @Id;

END

ネイティブ コンパイルされたこのプロシージャはクエリを受け取り、それを実行する .DLL コードを作成します。 ネイティブ コンパイルされたプロシージャを使用することで、データのクエリ実行と更新が速くなります。

Conclusion

SQL Server と SQL Database でネイティブ JSON 関数を利用すると、NoSQL データベースの場合と同様に JSON ドキュメントを処理できます。 リレーショナルであれ、NoSQL であれ、JSON データの処理に関しては、あらゆるデータベースに良し悪しがあります。 SQL Server または SQL Database に JSON ドキュメントを保存することの主な利点は SQL 言語の完全サポートにあります。 充実した Transact-SQL 言語を利用し、圧縮率の高い列ストア インデックス、高速の分析によるロックのない処理を可能にするメモリ最適化テーブルなど、さまざまストレージ オプションを構成できます。 同時に、発展したセキュリティ機能や国際化機能の利点が得られ、これらの機能は NoSQL シナリオで簡単に再利用できます。 この記事で説明されている理由は、SQL Server または SQL Database に JSON ドキュメントを保存する十分な理由となります。

SQL データベース エンジンでの JSON の詳細

組み込みの JSON サポートの視覚的な概要については、次のビデオを参照してください。

関連コンテンツ

  • Last updated on