OPENJSON（SQL Server）

ITコーディネータのシュウです。

上の写真は、少し前になりましたが、昨年、会社のイベントで山梨県の忍野八海に行ったときの写真です。富士山の伏流水に水源を発する湧水池である「忍野八海」は、天然記念物であり、世界遺産富士山の構成資産の一部として認定されているとのことで、前々から一度は行ってみたいと思っていたところでしたので、百聞は一見に如かずということで、行くことができてよかったです。それぞれの湧水は、思ったほど大きくはなく、一つだけ離れた「出口池」以外の７つの池を巡りました。その中で、とても深くて透明度が高い「湧池」というのが印象に残りました。また、途中、お昼ご飯に寄ったお食事処で食べた山梨名物のほうとうがとても美味しかったです。富士山が曇っていてよく見えなかったのが少し残念でした。

＜本日の題材＞
OPENJSON（SQL Server）

前回は、SQL ServerでJSONデータを利用できる件についての第1弾として、「RDBMSの表形式のデータをJSONフォーマットに変換して出力できる」ことについて、「FOR JSON」句を利用することで可能なことを確認してみました。
本日は、第2弾として、「JSONフォーマットで入力したデータを、RDBMSの表形式に変換して格納できる」という部分について、試してみたいと思います。
データの型について補足ですが、XML を SQL Server テーブルに取り込む際は「XML データ型」が使用できますが、JSON では JSONそのままの形で SQL Server に格納する「JSONデータ型」は用意されておらず、OPENJSON句を使うことで、表形式のデータに変換することができます。

例)
前回使用した、StudentテーブルのデータをJSON形式で出力した結果のテキストを使用してみたいと思います。そのデータの1行分のデータを、@json という変数に設定し、OPENJSON句を使ってSELECTすると、

DECLARE @json NVARCHAR(MAX)

SET @json='{"学籍番号":"20220001","氏名":"生徒A","性別":"男","生年月日":"2006-05-12","身長":175}';

SELECT * FOM OPENJSON(@json);

上記のように、key、value、type ということで、各項目の名前と値が、それぞれkey、value として抽出され、type には、valueの値がどのような型かを表わす数字が下記のように表示されます。
　文字列：1
　整数：2
　真偽値：3
　配列：4
　オブジェクト：5
　NULL：0

それでは、前回使用した、StudentテーブルのデータをJSON形式で出力した結果の全ての行を変数として読み込んで、行列形式で表示したい場合には、下記のように WITH句を使用します。

DECLARE @json NVARCHAR(MAX)
SET @json =
N'[
       {
         "学籍番号":"20220001",
         "氏名":"生徒A",
         "性別":"男",
         "生年月日":"2006-05-12",
         "身長":175
       },
       {
         "学籍番号":"20220002",
         "氏名":"生徒B",
         "性別":"女",
         "生年月日":"2007-02-21",
         "身長":158
       },
       {
         "学籍番号":"20220003",
         "氏名":"生徒C",
         "性別":"女",
         "生年月日":"2006-11-07",
         "身長":162
       },
       {
         "学籍番号":"20220004",
         "氏名":"生徒D",
         "性別":"男",
         "生年月日":"2006-07-18",
         "身長":182
       },
       {
         "学籍番号":"20220005",
         "氏名":"生徒E",
         "性別":"男",
         "生年月日":"2006-12-25"
       }
    ]'

SELECT * FROM
OPENJSON ( @json )
WITH (
         学籍番号   NVARCHAR(8),
         氏名       NVARCHAR(20),
         性別       NVARCHAR(1),
         生年月日   DATE,
         身長       int
);

上記のようにRDBのテーブルのように抽出されることから、一旦 dbo.Studentテーブルの値を削除した後、先ほどのJSONフォーマットの出力データから、Studentテーブルにデータを登録したいと思います。

TRUNCATE TABLE dbo.Student;

JSONフォーマットの出力データから、データを登録します。

DECLARE @json NVARCHAR(MAX)
SET @json =
N'[
              {"学籍番号":"20220001","氏名":"生徒A","性別":"男","生年月日":"2006-05-12","身長":175},
              {"学籍番号":"20220002","氏名":"生徒B","性別":"女","生年月日":"2007-02-21","身長":158},
              {"学籍番号":"20220003","氏名":"生徒C","性別":"女","生年月日":"2006-11-07","身長":162},
              {"学籍番号":"20220004","氏名":"生徒D","性別":"男","生年月日":"2006-07-18","身長":182},
              {"学籍番号":"20220005","氏名":"生徒E","性別":"男","生年月日":"2006-12-25"}
    ]';

INSERT INTO dbo.Student
SELECT * FROM
OPENJSON ( @json )
WITH (
         学籍番号   NVARCHAR(8),
         氏名       NVARCHAR(20),
         性別       NVARCHAR(1),
         生年月日   DATE,
         身長       int
);