SQL Server - OPENROWSETでJSONファイルを読み込んでOPENJSON

前回、OPENJSONを使ってJSON文字列を結果セットに変換してみました。

ichiroku11.hatenablog.jp

そのあとにそういえば「JSONファイル」を読み込んでOPENJSONを使う方法を知らないなと思いました。検索してみると次の記事が見つかります。

Importing JSON files into SQL Server using OPENROWSET (BULK) | SQL Server Database Engine Blog

この記事の二番煎じもいいところですが、OPENROWSETもCROSS APPLYもたぶんあまり使ったことがない気がするので試してみました。

まずJSONファイルを用意します。

{
    "entries": [
        { "id": 10, "name": "" },
        { "id": 30, "name": "" },
        { "id": 20, "name": "" }
    ]
}

JSONファイルはC:\Temp\sample.jsonにあるとします。このファイルを読み込んで結果セットに変換するクエリは次のように書きます。

select Id, Name
from
    -- OPENROWSETでJSONファイルを読み込む
    -- SINGLE_NCLOBを指定して1行1列の結果セットを取得する
    -- 結果セットにJson(Text)という名前を付ける
    openrowset(bulk N'C:\Temp\sample.json', SINGLE_NCLOB) as Json(Text)
    -- 結果セットのJson.Textに対してCROSS APPLYでOPENJSONを呼び出す
    cross apply
        openjson(Json.Text, N'$.entries')
        with(
            Id int N'$.id',
            Name nvarchar(10) N'$.name'
        )
order by Id;

-- 実行結果
/*
Id          Name
----------- ----------
10          あ
20          う
30          い
*/

OPENROWSETの引数にSINGLE_NCLOBを指定すると、ファイルの内容をnvarchar(max)の1行1列の結果セットとして取得できます。CROSS APPLYは結果セットの行ごとに関数を呼び出します。今回の結果セットは1行なのでOPENJSONは1回だけ呼ばれているはずです。

参考