SQL Server での JSON を表として扱う & インデックスのお話し #dekan

この前の 第8回 関西DB勉強会 - connpass で、
ついにリリース！！ MySQL 8.0 最新情報
こちらのお話しをされてたときに、 JSON_TABLE と Generated Column で JSON にインデックスを作る話しの箇所で会場が盛り上がってたので、 SQL Server でも同じこと出来るよーっていうお話し。

クエリ書いて紹介していくので、JSON が入ったテーブルを用意しときます。
一応 JSON しか入らないように CHECK制約掛けときます。

drop table if exists Table1
create table Table1 (
  loto6json nvarchar(200) not null 
    constraint C_Table1_JSON 
      check(isjson(loto6json) > 0)
)

insert into Table1 values 
('{"no":"第1回","date":"2000年10月5日","numbers":["02","08","10","13","27","30"],"bonus":"39"}'),
('{"no":"第2回","date":"2000年10月12日","numbers":["01","09","16","20","21","43"],"bonus":"05"}'),
('{"no":"第3回","date":"2000年10月19日","numbers":["01","05","15","31","36","38"],"bonus":"13"}'),
('{"no":"第4回","date":"2000年10月26日","numbers":["16","18","26","27","34","40"],"bonus":"13"}'),
('{"no":"第5回","date":"2000年11月2日","numbers":["09","15","21","23","27","28"],"bonus":"43"}')

最初は JSON_TABLE の方からいきましょう。
MySQL の JSON_TABLE はこんなのです。
MySQL :: MySQL 8.0 Reference Manual :: 12.16.6 JSON Table Functions
JSON を表形式にしてくれる感じなんですかね。

これと同等？の動きをするのは
OPENJSON (Transact-SQL) | Microsoft Docs
かな。
OPENJSON は テーブル値関数なので CROSS APPLY と併用する形になるかと。

select [no], [date], numbers, bonus 
from Table1 cross apply openjson(Table1.loto6json) 
  with (
    [no] nvarchar(10)
    , [date] nvarchar(11)
    , numbers nvarchar(max) '$.numbers' as json
    , bonus int
  )

これの結果がこんなのになります。

no	date	numbers	bonus
第1回	2000年10月5日	["02","08","10","13","27","30"]	39
第2回	2000年10月12日	["01","09","16","20","21","43"]	5
第3回	2000年10月19日	["01","05","15","31","36","38"]	13
第4回	2000年10月26日	["16","18","26","27","34","40"]	13
第5回	2000年11月2日	["09","15","21","23","27","28"]	43

numbers も分解したい場合はもう一回 OPENJSON 使う感じで。
データ多くなるので条件絞っときます。

select [no], [date], [value], bonus
from Table1 cross apply openjson(Table1.loto6json) 
  with (
    [no] nvarchar(10)
    , [date] nvarchar(11)
    , numbers nvarchar(max) '$.numbers' as json
    , bonus int
  ) nest1 cross apply openjson(nest1.numbers) 
  with ( [value] int '$' )
where [no] = N'第1回'

結果はこんな感じ。ちゃんと取れてますね。

no	date	value	bonus
第1回	2000年10月5日	2	39
第1回	2000年10月5日	8	39
第1回	2000年10月5日	10	39
第1回	2000年10月5日	13	39
第1回	2000年10月5日	27	39
第1回	2000年10月5日	30	39

次は Generated Column で JSON にインデックス の方にです。

MySQL でのお話しはここら辺っぽいです。
MySQL :: MySQL 8.0 Reference Manual :: 8.3.11 Optimizer Use of Generated Column Indexes

これと同等の動きをするのは 計算列でのインデックス ですね。
ドキュメントはこれかな？
Index JSON data | Microsoft Docs

SQL Server の計算列に対するインデックスについては、
Indexes on Computed Columns | Microsoft Docs
こちらをどうぞ。

では、計算列を作って index を貼ります。

alter table Table1 add 
  [date] as json_value(loto6json, '$.date')
create index idx_Table1_date on Table1([date])

実行すると警告出ますが無視します。

警告: 非クラスター化 インデックスの最大キー長は 1700 バイトです。インデックス 'idx_Table1_date' の最大長は 8000 バイトです。大きな値の組み合わせの一部では挿入操作または更新操作が失敗します。

計算列の結果が 8000 バイト超える場合は、エラーになってしまうよ！っていう警告です。今回は超えないのが分かっているので無視です。

データが少なすぎて index 使われないので適当にデータを増やしてからクエリを実行すると…

select loto6json from Table1 
where [date] = N'2000年10月5日'

こんな実行プランになりました。 Index Seek になってますね！

ちなみに 計算列 まで取ってしまうと、ここで計算します。

select * from Table1
where [date] = N'2000年10月5日'

ちなみに SQL Server の計算列には、PERSISTED オプションで永続化出来ますが、参照時に永続化の値を使うことは少なそうです。。
今回の例で試しましたが select で Compute Scalar してました。
調べると幾つか出てきますねー。
浮動小数点型の計算列でインデックスを貼りたい時と、パーティションテーブルで使う以外は PERSISTED は使わない感じかなー。
performance - Why does the Execution Plan include a user-defined function call for a computed column that is persisted? - Stack Overflow
Properly Persisted Computed Columns - SQLPerformance.com