OPENJSON で JSON を取り込む
前編では、SQL Server の表形式の入力を JSON フォーマットで出力する「FOR JSON」を説明しましたが、「OPENJSON」はその逆です。実際の例を見てもらった方が早いでしょう。以下は JSONのキーと値を OPENJSON のパラメーターとして渡した場合の例です。
SELECT の結果セットとして、key 列に JSON のキーが、value 列に JSON の値が格納されています。FROM 句に記載した「OPENJSON」のパラメーターとして渡した JSON フォーマットが表形式の結果に変換されている、ということです。結果セットの列名は 「key」「value」「type」の 3 つです。key 列は nvarchar(4000)、value 列は nvarchar(max)、type 列は tinyint 型として返されます。XML を SQL Server テーブルに取り込む際は「XML データ型」が使用できますが、JSON では JSONそのままの形で SQL Server に格納する「JSONデータ型」は用意されていません。
type 列は value 列がどのような型か表します。上記例の場合、value 列には「値」という文字列 (string) が格納されているため、type 列は文字列を示す 1 になります。その他にも type 列には整数であれば 2 が、配列であれば 4 が次の例のように格納されます。
一般的には、入力となる JSONの各値がどのような型であるかは事前に分かっており、type 列の値が必要となるケースは多くないと思います。ただ、JSON の入力値が動的に変化する場合は type 列の値を基に分岐処理を行えます。
例では SELECT で結果取得のみ行っていますが、SELECT した結果を SELECT INTO もしくは INSERT INTO SELECT でテーブル データとして格納できます。
OPENJSON の出力を整形する
業務アプリケーションで JSON を使用する場合、value の中にオブジェクトが何階層かネストされていたり、JSON 全体が数十行・数百行あったりすると思います。その場合、「JSON の特定の値のみデータベースに格納したい」「オブジェクトの値を key、value 列ではなく、それぞれ任意の列として出力したい」と思われるかもしれません。次は、その要望に応える機能を説明したいと思います。
まずは、JSON に含まれる特定の値のみ出力したい場合の対処です。OPENJSON はパラメーターを 2つ受け取ることができます。1つ目は今まで見てきた通り、JSON フォーマットの文字列を受け取ります。2つ目のパラメーターは、どの部分を出力対象とするかを指定するパスを受け取ります。
次の例の場合、”会員” というキーに “住所” オブジェクトがネストしています。”住所” オブジェクト内の値をそれぞれ取得したい場合、OPENJSON の 2 つ目のパラメーターにパスを記載することで該当部分のみ取得できます。『lax $.”会員”.”住所”』のうち、「$」は JSON フォーマットのルートを示します。つまり、ルート直下の “会員” というオブジェクトにネストされている “住所” オブジェクトを指定しています。「lax」というのはパスの表記モードを示します。もし、指定したパス (今回の場合は $.”会員”.”住所”) が存在しなかった場合、lax モードの場合は空の結果セットが返ってきてエラーにはなりません。lax モードの他に strict モードがあり、strict モードの際にパスが不正であった場合はエラーが返ります。尚、パス指定のモードは省略できます。省略した場合、既定で lax モードが選択されます。
次は、出力を任意の列に変更する対処についてです。OPENJSON は WITH 句を追加して出力を指定できます。
WITH 句がない場合は 「key」「value」「type」の列が結果セットとして返りますが、WITH 句を指定すると、例えば次の場合、「氏名」「都道府県」列が結果セットとして返ります。
ここまでの内容から、JSON フォーマットの指定した範囲の値を指定した列として取得して SQL Server に表形式で格納できることが分かります。
