SQL Server 2016のJSON連携

SQL Server 2016でJSONを扱えるようになったと言うので、試してみたが・・・
JSONを直接扱うための型があるわけではなく、JSONそのものはあくまでも、varchar型に格納するらしい。
結局T-SQLで、JSON文字列を解析して通常のテーブルデータのように扱えたり、通常のテーブル内容をJSONに変換できるだけの模様。ちょっとがっかり。

こんな感じ
— テーブル内容をJSON化
SELECT [EmpNo] ,[Name] ,[Birthday]
FROM [TestDB].[dbo].[TestTable] FOR JSON AUTO

結果
[{“EmpNo”:”0840303″,”Name”:”T.Ohwaki”,”Birthday”:”1964-02-03T00:00:00″},{“EmpNo”:”0980555″,”Name”:”T.Sumomo”,”Birthday”:”1988-05-15T00:00:00″}]

— JSON文字列をテーブルデータとして取得
SET @jsonstr = ‘[{“EmpNo”:”0840303″,”Name”:”T.Ohwaki”,”Birthday”:”1964-02-03T00:00:00″},{“EmpNo”:”0980555″,”Name”:”T.Sumomo”,”Birthday”:”1988-05-15T00:00:00″}]’
SELECT [EmpNo] ,[Name] ,[Birthday]
FROM OPENJSON(@jsonstr)
   WITH (EmpNo nchar(7), Name varchar(20), Birthday datetime )

結果

EmpNo Name Birthday
0840303 T.Ohwaki 1964-02-03 00:00:00.000
0980555 T.Sumomo 1988-05-15 00:00:00.000

— 2016/06/03 追記 —
ちなみに、JSON用のカラムにはJSON文法チェックの制約を掛けることが可能。
例えば、EmpNoとPropertiesからなる、テーブルで、PropertiesにJSON文法チェックを掛けるには

create table JSONTest(EmpNo nchar(7),
Properties nvarchar(max) CONSTRAINT JSONTest_PropertiesMustBeJSON CHECK (ISJSON(Properties) > 0), Primary Key(EmpNo))

といった感じ。

後、JSON用カラムに格納されたJSONのプロパティ値を取得するには、JSON_VALUE関数を使用する。

select EmpNo,JSON_VALUE(Properties,’$.”Name”‘),convert(datetime,JSON_VALUE(Properties,’$.”BirthDay”‘)) from JSONTest

当然、$.Address.Countryのような階層構造の末端プロパティを指定することも可能。
末端では無いプロパティ(上記の例では、$.Address)を指定するとNULLが返る。

他にJSON構造を文字列で返す、JSON_QUERYなどがある。
(配列や複合プロパティなど。末端のプロパティを指定すると、NULLが返る)

詳しくは、下記URLを参照。
https://blogs.technet.microsoft.com/dataplatforminsider/2016/01/05/json-in-sql-server-2016-part-1-of-4/

https://blogs.technet.microsoft.com/dataplatforminsider/2016/01/05/json-in-sql-server-2016-part-2-of-4/

https://blogs.technet.microsoft.com/dataplatforminsider/2016/01/05/json-in-sql-server-2016-part-3-of-4/

https://blogs.technet.microsoft.com/dataplatforminsider/2016/01/05/json-in-sql-server-2016-part-4-of-4/

たけぞう

たけぞう について

某ソフトウェア開発会社勤務。 開発の仕事は卒業しますた。 ・・・と思ったら、また開発に逆戻り(^^; ・・・と思ったら、また卒業らしい・・・
カテゴリー: SQL Server, 技術情報 パーマリンク

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です