次の方法で共有

JSON ドキュメントの格納

Applies to: SQL Server 2016 (13.x) and later versions Azure SQL DatabaseAzure SQL Managed InstanceSQL database in Microsoft Fabric

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

JSON ストレージの形式

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

  • LOB storage - JSON documents can be stored as-is in columns with the data type json or nvarchar. この方法では、読み込み速度が文字列型の列の読み込みに匹敵するため、データの読み込みと取り込みをすばやく行うのに最適です。 この方法では、クエリの実行中に未加工の JSON ドキュメントを解析する必要があるため、JSON 値のインデックス作成が実行されていない場合は、クエリ/分析時間のパフォーマンスがさらに低下する可能性があります。

  • Relational storage - JSON documents can be parsed while they are inserted in the table using OPENJSON, JSON_VALUE or JSON_QUERY functions. Fragments from the input JSON documents can be stored in the columns containing JSON sub-elements with data types json or nvarchar. この方法では、読み込み中に JSON の解析が行われるため読み込み時間が長くなります。ただし、クエリは、リレーショナル データに対する従来のクエリのパフォーマンスに匹敵します。

  • JSON データ型は現在、Azure SQL Database と Azure SQL Managed Instance のプレビュー段階にあります (Always-up-to-date 更新ポリシーで構成)。

  • 現在 SQL Server では、JSON はビルトインのデータ型ではありません。

Classic tables

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

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 シナリオには、この構造が最適です。

  • Use the native json data type where available to store JSON documents.
  • The nvarchar(max) data type lets you store JSON documents that are up to 2 GB in size. If you're sure that your JSON documents aren't greater than 8 KB, however, we recommend that you use nvarchar(4000) instead of nvarchar(max) for performance reasons.

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

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

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

テーブルに JSON ドキュメントを保存するとき、標準の Transact-SQL 言語を使用し、ドキュメントにクエリを実行できます。 For example:

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

It's a powerful advantage that you can use any T-SQL function and query clause to query JSON documents. SQL Server と SQL Database のクエリには、JSON ドキュメントの分析に利用できる制約がありません。 JSON_VALUE 関数で JSON ドキュメントから値を抽出し、それを他の値と同様にクエリで利用できます。

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

Indexes

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

指定のパスで (つまり、パス $.severity で) JSON 列から JSON 値を公開する計算列を作成し、この計算列で標準インデックスを作成できます。 For example:

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 つは、それが照合順序対応であることです。 If your original nvarchar column has a COLLATION property (for example, case-sensitivity or Japanese language), the index is organized according to the language rules or the case sensitivity rules associated with the nvarchar column. 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)

メモリ最適化テーブルは、頻繁に変更するドキュメントに最適な選択肢です。 メモリ最適化テーブルの利用を検討するとき、パフォーマンスについても考慮してください。 Use the nvarchar(4000) data type instead of nvarchar(max) for JSON documents in your memory-optimized collections, if possible, because it might drastically improve performance. The json data type is not supported with memory-optimized tables.

クラシック テーブルの場合と同様に、計算列を使用して、メモリ最適化テーブルで公開するフィールドでインデックスを追加できます。 For example:

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 値をできるだけ小さい型にキャスト変換します。 In the preceding example, tinyint is used.

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

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 サポートの視覚的な概要については、次のビデオを参照してください。

Related content