T-SQLにおけるOPENJSON-WITH句でException取り扱い

はじめに

OPENJSON-WITH句でちょっとハマったのでメモ。
jsonファイルを読み取ってその内容をDatabaseに一行ずつINSERTしたい。
異常なデータがあった場合、そのデータのみ失敗させてその他のデータは正常にINSERTする。

具体的に

こんな感じのjsonファイルがある。
これをOPENJSON句を使ってT-SQLで扱いたい。

テーブルの定義はすべてintで定義されてるので、2つ目のlengthのデータはINSERT時にoverflowする。
そのため、1つ目と3つ目のデータのみをINSERTするようにする。

sample.json

{"Size" : [
        {"Number": 1, "Width" : 1000, "Length" : 2000 } ,
        {"Number": 2, "Width" : 2000, "Length" : 3147483647} ,
        {"Number": 3, "Width" : 3000, "Length" : 2000 } 
    ]
}

処理のイメージとしては、jsonの中身をループさせて、overflowのExceptionが発生したらCONTINUEして次のJSONを実行してく。
例えばこんな感じ。
(動作確認していないので細かい部分が間違ってたらすいません)

sample.sql

DECLARE @i int = 1
WHILE @i <= 3
BIGIN
  BEGIN TRY
    INSERT INTO sampletable
    SELECT Number, Width, Length FROM OPENJSON(@json,'$.Size')
    WITH(Number int '$.Number', Width int '$.Width', Length int '$.Length')
    WHERE Number = @i
  END TRY
  BEGIN CATCH
    @i += 1;
    CONTINUE;
  END CATCH
    @i += 1;
END;

想像していた処理的には
2番目のデータの時にのみoverflowのExceptionが発生して、TRY-CATCHが行われCONTINUEされて次の文を処理する。

ただ実際の動作としては、すべてのループにおいてExceptionが発生してしまっていた。
どうやら、jsonのの中に一つでもoverflowする値が入っている場合、OPENJSON-WITH句でExceptionが発生してしまうようだ。

対策

Exception発生させたいのはOPENJSON-WITH句の部分ではなく、INSERTの部分なので
とりあえずjsonの変換はException発生しないよう応急処置

sample.sql

DECLARE @i int = 1
WHILE @i <= 3
BIGIN
  BEGIN TRY
    INSERT INTO sampletable
    SELECT Number, Width, Length FROM OPENJSON(@json,'$.Size')
    WITH(Number int 'Number', Width bigint '$.Width', Length bigint 'Length') --bigintにする
    WHERE Number = @i
  END TRY
  BEGIN CATCH
    @i += 1;
    CONTINUE;
  END CATCH
    @i += 1;
END;

これで想定した動作が行われた。

OPENJSON-WITH句ではWHEREは効かず、いったんjsonファイルをすべて変換している模様(?)
なのでWITH句の中の変換をとりあえず乗り切るためにintじゃなくてbigintに変換するというその場対応。
bigintで変換したとしてもINSERTするときは、結局テーブルの定義がintとしてあるのでException発生する。

まとめ

とりあえずいったんbigint型を経由することで想定した処理が実現できた。
ただあまり褒められた実装でないし、スマートでないし、納得もいっていない。

素直に、Jsonを一旦ListにDeserializeし、そのListをForeachで回して一行ずつINSERTしていく方が無難なんでしょうか。
WHILEループのたびにOPENJSON-WITHをでjson全体を展開していることも考えると、SQLの外で分解してあげた方が処理としても早いかもしれないですね。
(AzureFunctions内の処理でHTTPRequestbodyでjsonを受け取り、それをDatabaseにINSERTする流れを想定しています)

それともSQLでもっと簡単にできたりするんでしょうか。
もっと効率のいいやり方ご存じの方いましたらコメントお願いします。

追記

Json.Netを使ってjsonをListに変換してからforeachで回してINSERTした方が処理が早かったです。
ただJson.NetでDeserializeするときにも大きめの型を用意しないとExceptionが発生するので注意