概要
SQL ServerにこっそりとJSON対応の機能が追加されています。この記事ではその機能を簡単に紹介したいと思います。
JSON対応ということで気になるのは「型はどうなるの？新しくJSON型とか増えるの？」というところですが増えません。従来のNvarchar型に格納されているJSON形式の文字列をいろいろできるようになります。
JSON形式での出力
クエリの結果を簡単にJSON形式にすることができます。新しく追加されたFOR JSONを使用することで可能です。
こう書くと
select Name, Age, Address, EMail from Person FOR JSON
こんな感じで文字列が取得できます。
[
{ "Name" : "Joe", "Age" : 22, "Address" : "Tokyo", "EMail" : "joe@mail.com" } ,
{ "Name" : "Tom", "Age" : 25, "Address" : "Paris", "EMail" : "tom@mail.com" } ,
{ "Name" : "Mike", "Age" : 30, "Address" : "Sanfrancisco", "EMail" : "mike@mail.com" }
]
WEB APIなどでJSON形式で文字列を返す場合などに便利です。
OPENJSON
今度はJSON形式の文字列をテーブルに変換し、FROM句で活用する方法を紹介します。OPENJSONを使用するとJSON形式の文字列をテーブルに変換できます。
一つ注意点があります。OPENJSONは互換レベルが130以上でないと使用できません。設定を変更するには以下のクエリを対象のDBに対して実行します。
ALTER DATABASE DatabaseName SET COMPATIBILITY_LEVEL = 130
そうするとOPENJSONが使用できるようになります。
使い方はこんな感じです。例えば以下のようなJSON形式の文字列があるとします。
'{ "Persons" : [
{ "Name" : "Joe", "Age" : 22, "Address" : "Tokyo", "EMail" : "joe@mail.com" } ,
{ "Name" : "Tom", "Age" : 25, "Address" : "Paris", "EMail" : "tom@mail.com" } ,
{ "Name" : "Mike", "Age" : 30, "Address" : "Sanfrancisco", "EMail" : "mike@mail.com" }
]
}'
この文字列が@JsonTextという変数に入っています。それをテーブルとして利用するには
select * from OPENJSON(@JsonText, '$.Persons')
WITH (Name nvarchar(100))
複数列取得するには
select * from OPENJSON(@JsonText, '$.Persons')
WITH (
Name nvarchar(100),
Age int
)
この関数を使用することでJSON形式で渡された値をきちんとスキーマがあるテーブルにINSERTするのが簡単にできます。例えば
Create Table Person
(CD UniqueIdentifier Not Null
,Name Nvarchar(100) Not Null
,Age Int Not Null
,Address Nvarchar(max) Not Null
,EMail Nvarchar(max) Not Null
,Constraint Person_PrimaryKey Primary Key Clustered(CD)
)
というテーブルがあったとして以下のように簡単にINSERTができます。
INSERT INTO Person
select NEWID(),* from OPENJSON(@JsonText, '$.Persons')
WITH (
Name nvarchar(100),
Age int,
Address Nvarchar(max),
EMail Nvarchar(max)
)
非常に魅力的な機能ですね。
ISJSON
ISJSONを使用すると引数で渡した文字列がJSON形式かどうかを0,1で返してくれます。これを使用することでJSON形式の文字列とJSON形式以外の文字列が混在して格納されているときにJSON形式の文字列のみ抽出することが可能です。
select * from Person
where ISJSON(JsonColumn) > 0
JSON_VALUE
JSON_Valueを使用するとJSON形式のオブジェクトの指定したプロパティの値を取得できます。
例えば以下のようなJSON形式の文字列がNvarcharのJsonColumn列に格納されているとします。
{
"Name" : "Joe",
"Age" : 22,
"Address" : {
"Country" : "Japan",
"Town" : "Tokyo",
"PostCode" : "130-0011"
}
}
JSON_VALUEを使用すると指定したプロパティの値を取得可能です。
名前、年齢、住んでいる町を取得するには。
select
JSON_VALUE(JsonColumn, '$.Name') as Name
JSON_VALUE(JsonColumn, '$.Age') as Age
JSON_VALUE(JsonColumn, '$.Address.Town') as Town
from Person
where ISJSON(JsonColumn) > 0
と書けば指定したパスのプロパティの値だけを取得できます。
JSON_QUERY
JSON_QUERYを使用するとJSON形式のオブジェクトもしくは配列としてデータを取得できます。
select
JSON_QUERY(JsonColumn, '$.Address') as Address
from Person
where ISJSON(JsonColumn) > 0
と書くとAddress部分に当たる文字列の
{ "Country" : "Japan", "Town" : "Tokyo", "PostCode" : "130-0011" }
という文字列が取得できます。
JSON_MODIFY
JSON_MODIFYを使用すると指定したプロパティの値を変更し、変更後の値を戻り値として取得できます。PersonテーブルのJsonColumn列に以下のデータが格納されているとします。
{
"ID" : 123
"Name" : "Joe",
"Age" : 22,
"Address" : {
"Country" : "Japan",
"Town" : "Tokyo",
"PostCode" : "130-0011"
}
}
--変更前の値を取得します。
SET @OldValue = select JsonColumn from Person where JSON_VALUE(JsonColumn, '$.ID') = 123
--JSON_MODIFYを使ってプロパティの値を変更しその値を変数に格納します。
SET @NewValue = JSON_MODIFY(@OldValue, "$.Address.Town", 'Fukuoka')
update Person
set JsonColumn = @NewValue
where JSON_VALUE(JsonColumn, '$.ID') = 123
もちろんもっとまとめて書いてUPDATE処理を行うこともできます。解析処理を書くことなく変更したいプロパティのパスを指定するだけでいいので便利です。
JSON機能のまとめ
まとめると
・FOR JSON --- テーブルをJSON文字列に
・OPENJSON --- JSON文字列（配列）をテーブルに
・ISJSON --- JSON文字列かどうかチェック
・JSON_VALUE --- 指定したプロパティの値を取得
・JSON_QUERY --- 指定したパスのJSONオブジェクトを取得
・JSON_MODIFY --- 指定したプロパティを修正
という感じです。
JSONを使用しているシステムは多いと思います。機会があれば是非活用してみてください。