SQL Server 2016でJSONに対応します

MSDNブログで投稿されていた「JSON support in SQL Server 2016」が元ネタ。

SQL ServerでのJSONサポートは、Microsoft connect siteで、1000以上の投票が集まるリクエストが多いもののひとつです。マイクロソフトは、SQL Server 2016で、デフォルト機能でJSONサポートをすることを発表しました。

まず最初に、nativeのJSONサポートは、nativeのJSONデータ型を提供するわけではありません。SQL Server 2016では、JSONは、NVARCHARデータ型で表現される予定です。

移行
すでに多くの人がテキストとしてJSONを格納しており、JSONデータ型を提供した場合、新しい機能を使用するには、データベーススキーマーの変更とデータの再ロードが必要になってしまいます。開発者がデータベースの変更をすることなく新しい機能を利用できるように実装します。
機能間の互換性
NVARCHARは全てのSQL Serverのコンポーネントでサポートしているので、JSONがどこでもサポートされます。Hekaton、カラムストアテーブル、ローレベルセキュリティを含む標準セキュリティポリシー、標準のB-Treeインデックス、FTSインデックス、ストアドプロシージャーでのJSON利用などができます。Hekatonとカラムストアはは、LOB値に対応していないので、少ないデータ量のJSONドキュメントのみ対応します。
クライアントサイドのサポート
C#では、多くの開発者がJSON.NETを使用してビルトインのJObjectやJArray型を使用しています。しかし、まだ標準ではありません。JSONデータ型を追加したとしても、クライアントアプリでは文字列として扱われ、必要があればカスタムパーサーが使われるでしょう。

JSONデータのエクスポート

最初の機能提供は、SQL Server 2016 CTP2で提供されており、「FOR JSON」を使用してJSON形式で結果を取得できるようになっています。

SELECT column, expression, column as alias FROM table1, table2, table3 FOR JSON [AUTO | PATH]

T-SQLクエリの最後に、「FOR JSON」を追加すると、JSONテキスト形式の結果をクライアントに返します。各行は全て、JSONオブジェクトの一部として整形され、セルの値はJSONオブジェクトの値として生成されます。

JSONテキストをリレーショナルテーブルに変換する：OPENJSON

OPENJSONは、JSONテキストを探すテーブル値機能（TVF）です。

SELECT Number, Customer, Date, Quantity
 FROM OPENJSON (@JSalestOrderDetails, '$.OrdersArray')
 WITH (
        Number varchar(200),
        Date datetime,
        Customer varchar(200),
        Quantity int
 ) AS OrdersArray

@JSalesOrderDetailsは、次のような例のOrdersArrayプロパティでJSONオブジェクトの配列を含むテキストです。

‘{"OrdersArray": [

   {"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}
]}’

OPENJSONは、プロパティで配列を見つけ、JSONオブジェクトごとに行を返します。結果セットには、4列含まれ、定義は、「WITH」で指定します。OPENJSONは、Number、Date、Customer、Quantityプロパティを見つけ、結果セットのカラムに値を変換します。デフォルトでは、プロパティが見つからない場合はNULLを返します。

JSONを処理するビルトイン関数

ISJSON

NVARCHARテキストにJSONと互換性があるか確認します。
JSON_VALUE

JsonTextをパースし、JSONパスで指定した値を取得します。
JSON_QUERY

JsonTextをパースし、JSONフラグメントを取得します。

SELECT t.Id, t.OrderNumber, t.OrderDate,
 JSON_VALUE(t.JOrderDetails, '$.Order.ShipDate'),
 JSON_QUERY(t.JOrderDetails, '$.Order')
FROM SalesOrderRecord AS t
WHERE ISJSON(t.JOrderDetails) > 0
 AND JSON_VALUE(t.JOrderDetails, '$.Order.ProductType') = 'TV'

JSON インデックス

今のところJSON用のカスタムインデックスを追加する予定はありません。

JSONはプレーンなテキストで、既存のB-Treeやフルテキスト検索インデックスを使用できます。

たとえば次のように、JSON_VALUE関数を使用して、計算列を作成し、その列に標準のインデックスを作成します。

CREATE TABLE SalesOrderRecord (
 Id int PRIMARY KEY IDENTITY,
 OrderNumber NVARCHAR(25) NOT NULL,
 OrderDate DATETIME NOT NULL,
 JOrderDetails NVARCHAR(4000),
 Quantity AS CAST(JSON_VALUE(JOrderDetails, '$.Order.Qty') AS int),
 Price AS JSON_VALUE(JOrderDetails, '$.Order.Price'),
 )
 GO
 CREATE INDEX idxJson
 ON SalesOrderRecord(Quantity)
 INCLUDE (Price);

JSONデータのエクスポート

JSONテキストをリレーショナルテーブルに変換する：OPENJSON

JSONを処理するビルトイン関数

JSON インデックス

コメントを残す コメントをキャンセル

コメントを残すコメントをキャンセル