SQL Server 2016 では、JSONへのサポートが追加されました。
どのような考え方でJSONサポートが追加されていて、どのようにユーザーはJSONをSQL Serverで使用できるのかを理解するのに、SQL Server チームが投稿した全4回の投稿が参考になるので、ざっくりと意訳してみました。
概要
SQL Server でJSON関数は、JSONデータを分析しクエリを実行し、JSONをリレーショナルフォーマットに変換し、SQLクエリ結果をJSONテキストに出力します。
組み込み関数（JSON_VALUE、JSON_QUERY）を使用して、JSONからデータを取得したり、JSONフォーマットが正しいかを検証できます。OPENJSON関数を使用するとJSONオブジェクト配列を行セットに変換できます。FOR JSONでJSONテキスト形式でクエリ結果を取得できます。
次のTransact-SQLコードは、JSONテキストの格納にテキスト変数を指定しています。
DECLARE @json NVARCHAR(4000)
SET @json =
N'{
“info”:{
“type”:1,
“address”:{
“town”:”Bristol”,
“county”:”Avon”,
“country”:”England”
},
“tags”:[“Sport”, “Water polo”]
},
“type”:”Basic”
}’
JSON_VALUE と JSON_QUERY関数を使用して、JSONテキストから値とオブジェクトを取得できます。
SELECT
JSON_VALUE(@json, ‘$.type’) as type,
JSON_VALUE(@json, ‘$.info.address.town’) as town,
JSON_QUERY(@json, ‘$.info.tags’) as tags
このクエリは、「Basic」、「Bristol」、「[“Support”,”Water polo”]」を返します。JSON_VALUE関数は、セカンドパラメーターで指定したJSONパスでJSONテキスト（例えば、文字列、数字、true/false）から値を一つ返します。JSON組み込み関数はセカンドパラメーターで指定するJavaScriptのような文法でJSONの値やオブジェクトを参照します。
OPENJSON関数はJSONテキストの配列を参照でき、配列から項目を返します。
SELECT value
FROM OPENJSON(@json, ‘$.info.tags’)
この例では、タグ配列から文字列を返します。OPENJSON関数は複雑なオブジェクトを返します。
FOR JSONはSQLクエリによって返す結果セットをJSONテキスト形式で整形します。
SELECT object_id, name
FROM sys.tables
FOR JSON PATH
SQL ServerにJSONデータを格納する
SQL ServerではJSONはテキスト形式で格納されます。
JSONデータを格納するために標準的なNVARCHARカラムが使用されます。
次の例は、いくつかの情報をJSON形式で保存したシンプルなテーブルです。
CREATE TABLE Person (
Id int IDENTITY PRIMARY KEY NONCLUSTERED,
FirstName nvarchar(100) NOT NULL,
LastName nvarchar(100) NOT NULL,
InfoJson nvarchar(max)
) WITH (MEMORY_OPTIMIZED=ON)
伝統的なリレーショナルのみのデータベースやドキュメントのみのシステムと、SQL Server 2016で提供するハイブリッドモデルとの大きな違いがこの例で示されています。SQL Server 2016では、通常の列（この例では、FirstNameやLastName）とJSONを格納した列（この例では、InfoJSON）を混在させることができます。
SQL Serverでは、必要に応じて通常の列やJSON列、Spatial、XML列と1つ以上混在させて構成することができます。同じテーブルで速くアクセスできる標準的なリレーショナル列と柔軟性を提供するJSON列により迅速なアプリケーション開発ができます。
JSONはテキスト列に格納されますが、平文ではありません。SQL Serverは、UNICODE圧縮のような最大50%の圧縮ができる圧縮メカニズムを使用してテキスト列のストレージを最適化する仕組みを組み込んでいます。またカラムストアテーブルにJSONテキストを格納できたり、またはGZipアルゴリズムを使う組み込みのCOMPRESS機能で明示的に圧縮することができます。
JSONはSQL ServerのコンポーネントやNVARCHARデータで動作するテクノロジーと完璧な互換性があります。上の例では、JSONは高パフォーマンスを提供するインメモリOLTP（Hekaton）テーブルに格納しています。JSONを標準的なテーブルや、カラムストアインデックス、FILESTREAMに格納できます。Polybaseを使用してHadoopから読み込んだり、ファイルシステムから読み込んだり、Azure SQLにストレッチデータベースを設定できます。
もし自由なテキストフォーマットでJSONを保持したくない場合は、標準的なCHECK制約とISJSON関数を使用して正しいフォーマットになっているかのJSON検証妥当性確認を追加できます。
ALTER TABLE Person
ADD CONSTRAINT [Content should be formatted as JSON]
CHECK ( ISJSON( InfoJSON )> 0 )
こては標準的なチェック制約を使用して、正しいフォーマットのJSONカラムにテキストが格納されているかどうかの妥当性確認ができます。
JSONはテキストで表すので、クライアントアプリケーションを変更したり、新しいドライバーをまったり、プロトコルを変更する必要がありません。C#、Java、Node.jsアプリケーションでJSONドキュメントの読み書きができます。JSONはテキストフィールドでORMモデルで読み込んだり、Ajaxリクエストを経由して直接JavaScriptクライアントコードに送信できます。
組み込みのJSON処理用の関数
SQL Server 2016 では、JSONテキストをパースしたり処理する関数が提供されています。
- ISJSON(json文)：NVARCHARテキストがJSON仕様に沿った正しいフォーマットかを確認します。JSONを格納するNVARCHAR列に、この関数を使用してチェック制約を作成できます。
- JSON_VALUE(json文,パス)：JSONをパースして、JavaScriptライクなパスで指定された値を出力します。（JSONパス例は下を参照）
- JSON_QUERY(json文,パス)：JSONをパースして、JavaScriptライクなパスで指定されたオブジェクトまたは配列を出力します。（JSONパス例は下を参照）
これらの関数はJSONパスを使用してJSONテキストで値やオブジェクトを参照します。JSONパスはJSONテキストのプロパティの参照をJavaScriptライクな文法を使用します。いくつかの例を提示します。
- $ – 入力されたテキストのJSONオブジェクト全体を参照します
- $.property1 – JSONオブジェクトのproperty1を参照します
- $[4] – JSON配列の5番目の項目を参照します（インデックスはJavaScriptのように0から数えます）
- $.property1.property2.array1[5].property3.array2[15].property4 – JSONオブジェクトで複雑にネストしたプロパティを参照します
- $.info.”first name” – infoオブジェクトの”first name”プロパティを参照します。スペースやダラーなどの特別な記号がキーに含まれている場合、ダブルコーテーションマークで囲む必要があります。
$記号は、入力したJSONオブジェクト（XPATH言語ではルート”/”に該当します）を示します。JSONオブジェクトでプロパティを参照するために、$の後にJavaScriptライクのプロパティか配列を追加できます。次の例は、組み込み関数を使用してシンプルなクエリ例の1つです。
SELECT Id, FirstName, LastName,
JSON_VALUE(InfoJSON, ‘$.info.”social security number”’) as SSN,
JSON_QUERY(InfoJSON, ‘$.skills’) as Skills
FROM Person AS t
WHERE ISJSON( InfoJSON ) > 0
AND JSON_VALUE(InfoJSON, ‘$.Type’) = ‘Student’
このクエリは、FirstNameとLastNameを標準的なテーブル列から返し、JSON列からスキルの配列と社会保障番号を返します。結果は、InfoJSON列に正しいJSONが格納されていて、Type値にStudentを含んでる行を結果として返します。クエリの一部でJSONの値を使用できます。order byやgroup byなど。
JSONテキストをリレーショナルテーブルへ変換する – OPENJSON
OPENJSONは、JSONテキストの中を見たり、JSONオブジェクトの配列を示したり、主力結果に各エレメントを1行で戻したりするテーブル値関数（TVF）です。
上の例では、JSON配列のどこを開くか（例では、$.Ordrrs）、どの列を結果で返し、JSONオブジェクトの値をセルのどこに返すかを指定します。
次の例では、@orders変数のJSON配列を行セットにし、標準的なテーブルに挿入します。
INSERT INTO Orders(Number, Date, Customer, Quantity)
SELECT Number, Date, Customer, Quantity
OPENJSON (@orders)
WITH (
Number varchar(200),
Date datetime,
Customer varchar(200),
Quantity int
) AS OrdersArray
OPENJSONによって返される結果セットの4列は、WITHで定義されます。OPENJSONはNumber、Date、Customer、Quantityプロパティを探索し、それらの値を結果セットの列に変換します。既定では、プロパティが無ければNullを返します。上のクエリではｍ、@orders変数に次のJSON配列が含まてれいると仮定しています。
‘[
{“Number”:1, “Date”: “8/10/2012”, “Customer”: “Adventure works”, “Quantity”: 1200},
{“Number”:4, “Date”: “5/11/2012”, “Customer”: “Adventure works”, “Quantity”: 100},
{“Number”:6, “Date”: “1/3/2012”, “Customer”: “Adventure works”, “Quantity”: 250},
{“Number”:8, “Date”: “12/7/2012”, “Customer”: “Adventure works”, “Quantity”: 2200}
]’
JSONテキストからリレーショナル形式に変換するのはとても簡単なのがわかるかと思います。列名とデータ型と、JSONテキストでマッチしたプロパティを見つけたOPENJSONの列を指定する必要があります。
OPENJSONは、リレーショナルとJSONデータを同じクエリで組み合わせて使用することができます。前掲のJSONテキストがOrder列に格納されていると仮定して、次のクエリはJSONと列を組み合わせたクエリです。
SELECT Id, FirstName, LastName, Number, Date, Customer, Quantity
FROM Person
CROSS APPLY OPENJSON (OrdersJson)
WITH (
Number varchar(200),
Date datetime,
Customer varchar(200),
Quantity int ) AS OrdersArray
OPENJSONは、各セルに配列を開き、JSONオブジェクトごとに1行を配列で返します。CROSS APPLY OPENJSON文法は、JSONセル内のJSON配列からマテリアライズされる子内部表と表の行を結合するのに使われます。
JSONデータのインデックス
JSON値はテキストでフォーマットされますが、テーブル列にほかの値と同じ方法でインデックスすることができます。標準的な非クラスタ化インデックスまたはフルテキスト検索インデックスを使用できます。
クエリでよく使用されるいくつかのJSONプロパティにインデックスを作成したい場合、値を参照する非永続型の計算列を作成し、その列に標準的なインデックスを作成します。次の例は、InfoJSON列で$.Companyプロパティを使用して行をフィルタリングするクエリを最適化しています。
ALTER TABLE Person
ADD vCompany AS JSON_VALUE(InfoJSON, ‘$.Company’)
CREATE INDEX idx_Person_1
ON Person(vCompany)
SQL Serverはハイブリッドモデルを提供し、JSONからの値をキーや列に含めることができ、JSON値と標準的な列を同じインデックスで使用できます。
JSONは通常のテキストなので、標準的なフルテキスト検索を使用できます。フルテキストインデックスは、値の配列上で作成されます。フルテキストインデックスをJSON配列を含んだ列上で作成したり、既存の列でいくつかの配列を参照する計算列を作成しそのカラム上でフルテキスト検索インデックスを作成できます。
ALTER TABLE Person
ADD vEmailAddresses AS JSON_QUERY(InfoJSON, ‘$.Contact.Emails’)
CREATE FULLTEXT INDEX ON Person(vEmailAddresses)
KEY INDEX PK_Person_ID ON jsonFullTextCatalog;
フルテキスト検索インデックスはJSONはいてるに含まれているいくつかの値を含んでいる行を探そうとするクエリを最適化する必要があるときに便利です。
SELECT PersonID, FirstName,LastName,vEmailAddresses
FROM Person
WHERE CONTAINS(vEmailAddresses, ‘john@mail.microsoft.com’)
このクエリは、email配列に「john@mail.microsoft.com」を含んでいるPerson行を返します。フルテキストインデックスは、JSONのパースに関して特別なルールを用意していません。セパレーター（ダブルクォートやコンマ、括弧）を使用して、JSON配列を分割し、配列に値をインデックスします。フルテキストインデックスは、適切に数字やシンプルな文字列値に配列します。JSON配列でより複雑なオブジェクトを持っている場合、システムはKeyとValueの違いを認識できないのでフルテキストインデックスは直接適用できません。
ここまでで、同じインデックスで、JSON値とリレーショナル列両方を使用できることが分かりました。
データをJSONとして出力する – FOR JSON
SQL SELECT クエリの終わりに、FOR JSONを追加すると、SQL Serverは、JSONにフォーマットした結果をクライアントに返します。全ての行は1つのJSONオブジェクトにフォーマットされ、結果セットのセル内の値はJSONオブジェクトのvalueとして生成され、カラム名またはエイリアス名じゃKye名として生成されます。FOR JSONには2種類あります。
- FOR JSON PATH：カラム名/エイリアス名を使用してJSONアウトプットの構造を定義できます。列エイリアスでドットで区切られた文字列を指定すると、JSONプロパティはネーミング規則に従います。この機能はスラッシュを使用してパスを分割するFOR XML PATHと似ています。
- FOR JSON AUTO：クエリで使用したテーブル仮想をもとに、ネストしたJSONサブ配列を自動的に生成します。
FOR JSONで生成されたJSONテキストは、OPENJSONを使用してリレーショナル形式に戻す返還ができます。
まとめ
SQL ServerのJSON関数はJSONをリレーショナルに変換、リレーショナルデータをJSONに返還するのと同様のJSON分析とクエリ発行を可能にします。アプリケーションレイヤーで追加の変更なしにJSONデータを使ったり発行できるようになりました。
SQL ServerはリレーショナルデータとJSONを組み合わせたハイブリッドストレージモデルを提供します。高速データアクセスと柔軟で迅速な開発とのトレードオフを可能にしました。標準的な列とJSONテキストの値の両方で同じインデックス技術を使用できます。
ハイブリッドモデルは、SQL Serverのメリットである、フルパワーのクエリ言語とACIDトランザクションの恩恵を受けられます。SQL Serverと互換性のあるエコシステムであるツールを使用でき、既知の管理モデル、セキュリティモデルが、JSON機能でもそのまま使用できます。