Build 2024 のタイミングで次のアナウンスがありました。
Public Preview として、JSON データ型が発表されており、最近、実際に使用することができるようになりました。
Contents
JSON データ型の特徴
JSON データ型の特徴としては次のドキュメント / ドキュメントツリーに記載されています。
従来まで JSON フォーマットのデータを格納する際には、「nvarchar(max)」のようなテキストデータとして格納をしていましたが、JSON データ型を使用することで JSON フォーマットのデータをネイティブに格納することができるようになります。
特徴としては次のような内容があります。
- 格納されているデータが JSON フォーマットであることが保証される
- nvarchar を使用している場合は、ISJSON を使用して JSON として妥当かどうかを判断する必要があった
- JSON データ型の場合、データ登録時に JSON フォーマットであるかの検証が自動的に行われる
- 圧縮されてデータが格納される
JSON データ型ではデータが圧縮され格納が行われるため、従来の nvarchar にデータを格納する方法より、効率的にデータを格納することができることになっています。
サポートされる互換性レベル
SQL Server では、2016 から JSON サポートの強化が行われています。
JSON を操作するための関数である OPENJSON については、互換性レベルが 130 (SQL Server 2016) 以上という制限があり、互換性レベルの設定によっては、SQL Database (または、最新バージョンの SQL Server) を使用していても利用することができないというケースがありました。
JSON データ型については、利用可能な機能 に記載されていますが、すべての互換性レベルで使用することができます。
JSON は、すべてのデータベース互換レベルで使用できます。
そのため、JSON データ型については、最新のデータベース互換性レベルを使用していない環境でも利用することができます。
従来の文字列データとの格納効率の違い
JSON データ型の特徴の一つとして「圧縮用に最適化された、より効率的なストレージ」があります。
従来から活用されていた文字列データ型 (本検証では nvarchar) と、JSON データ型で、実際にどの程度データの格納効率が違うのかを確認してみました。
今回はログデータが JSON で格納されているテーブルを使用して検証を行っています。
このテーブルには「1,194,211 件」(119 万件) のデータを「ページ圧縮」をした状態で格納されています。
nvarchar(max) のデータ格納効率
上記の「jsonALL」のフィールドは「nvarchar(max)」で格納しているのですが、この場合は 8.3 GB 使用されています。
JSON データ型のデータ格納効率
それでは、これを JSON データ型で格納してみます。
データ件数は同等となっていますが、データサイズについては「8.3 GB → 4GB」まで削減されていることが確認できます。
JSON データ型は圧縮の設定はなく、データ格納時に自動的に圧縮された状態となるようです。(非圧縮とすることはできないようです)
上から nvarchar(max) 非圧縮 / nvarchar(max) ページ圧縮 / json 非圧縮 / json ページ圧縮 のサイズとなりますが、JSON データ型を使用した場合はデータの格納効率が高くなっていますね。
JSON データ型はネイティブに JSON フォーマット / 圧縮をサポートしており、JSON フォーマットのデータを格納する際には効率的に格納ができるので活用できるケースがいくつか出て来るのではないでしょうか。
JSON データに対しての検索の効率化
(n)varchar / json に JSON の文字列を格納した場合、JSON_VALUE 等の JSON 関数を使用して、JSON フォーマットの文字列の操作を行います。
例としては次のようなクエリとなります。
SELECT COUNT(*) FROM frontdoor.accesslog2 WHERE json_value (jsonAll, '$.securityProtocol_s') = 'TLS 1.2' GO
文字列 / JSON データ型に対して JSON_VALUE を使用した検索を行うと、データ型にかかわらず Scan が行われています。
JSON データ型は従来の文字列型と比較して、データの圧縮効率が高く成り、Scan 時にアクセスされるページ数は減少します。
しかし、格納されている JSON ドキュメントの要素に対して、インデックスが付与されているわけではありませんので、JSON フォーマットの文字列に対して単純な検索を行っただけでは検索の効率は低いものとなります。
計算列を使用したインデックスの付与
JSON の文字列に対して Seek による検索を実行する場合、JSON データ型を使用していても従来通り、検索に使用する項目に対して計算列を追加したインデックス付与を検討する必要があります。
ALTER TABLE frontdoor.accesslog6 ADD securityProtocol_s AS json_value (jsonAll, '$.securityProtocol_s') GO CREATE INDEX NCIX_json_securityProtocol_s ON frontdoor.accesslog6(securityProtocol_s) GO
計算列に対してインデックスを付与することで、実態を持つ列として定義が行われるため、検索を実施すると Seek が可能となります。
2024/7 時点ではドキュメントに記載されているが未実装の内容
本投稿を書いている時点の Store JSON documents in SQL Server or SQL Database では次の記載があります。
JSON データ型 + 列ストアインデックス / JSON データ型 + メモリ最適化テーブルのクエリのサンプルがありますが、2024/07/04 時点ではこれらの組み合わせはサポートされておらず、ドキュメントに記載されているクエリは動作しません。
今後、これらの組み合わせがサポートされる可能性があるかもしれませんが、現時点のプレビューでは、これらの組み合わはサポートされていないため以下のエラーが発生します。。(SR で確認したところ、JSON データ型 + メモリ最適化テーブルについては現時点では実装は予定されていませんが、JSON データ型 + 列ストアインデックスについては今後実装を予定しているとのことでした)
メッセージ 35343、レベル 16、状態 1、行 3
The statement failed. Column ‘log’ has a data type that cannot participate in a columnstore index.メッセージ 10794、レベル 16、状態 80、行 1
The type ‘json’ is not supported with memory optimized tables.
この件については、現状ドキュメントバグとなってしまっていることはMicrosoft に報告済みですので、今後、必要に応じて何らかのドキュメント修正が行われるのではないでしょうか。