Azure SQL Database の JSON 機能の概要Getting started with JSON features in Azure SQL Database

Azure SQL Database では、JavaScript Object Notation (JSON) 形式で表されたデータを解析およびクエリし、リレーショナル データを JSON テキストとしてエクスポートすることができます。Azure SQL Database lets you parse and query data represented in JavaScript Object Notation (JSON) format, and export your relational data as JSON text. Azure SQL Database では次の JSON のシナリオを使用できます。The following JSON scenarios are available in Azure SQL Database:

JSON 形式でのリレーショナル データの書式設定Formatting relational data in JSON format

データベース層からデータを取得し、JSON 形式で応答を提供する Web サービスがある場合、またはクライアント側の JavaScript フレームワークまたはライブラリが JSON 形式のデータを受け入れる場合、SQL クエリに直接 JSON としてデータベースの内容を書式設定することができます。If you have a web service that takes data from the database layer and provides a response in JSON format, or client-side JavaScript frameworks or libraries that accept data formatted as JSON, you can format your database content as JSON directly in a SQL query. Azure SQL Database からの結果を JSON 形式にするように、アプリケーション コードを書き込んだり、表形式のクエリの結果に変換して、オブジェクトを JSON 形式にシリアル化するために JSON シリアル化ライブラリを含めたりする必要がなくなりました。You no longer have to write application code that formats results from Azure SQL Database as JSON, or include some JSON serialization library to convert tabular query results and then serialize objects to JSON format. 代わりに、FOR JSON 句を使用して、SQL クエリの結果を Azure SQL Database で JSON として書式設定し、アプリケーションで直接これを使用できます。Instead, you can use the FOR JSON clause to format SQL query results as JSON in Azure SQL Database and use it directly in your application.

次の例では、FOR JSON 句を使用して、Sales.Customer テーブルの行を JSON 形式にしています。In the following example, rows from the Sales.Customer table are formatted as JSON by using the FOR JSON clause:

select CustomerName, PhoneNumber, FaxNumber
from Sales.Customers
FOR JSON PATH

FOR JSON PATH 句は、クエリの結果を JSON テキストとして書式設定します。The FOR JSON PATH clause formats the results of the query as JSON text. 次のように、セルの値が JSON 値として生成される場合、列名はキーとして使用されます。Column names are used as keys, while the cell values are generated as JSON values:

[
{"CustomerName":"Eric Torres","PhoneNumber":"(307) 555-0100","FaxNumber":"(307) 555-0101"},
{"CustomerName":"Cosmina Vlad","PhoneNumber":"(505) 555-0100","FaxNumber":"(505) 555-0101"},
{"CustomerName":"Bala Dixit","PhoneNumber":"(209) 555-0100","FaxNumber":"(209) 555-0101"}
]

結果セットは、JSON 配列として書式設定され、各行は個別の JSON オブジェクトとして書式設定されます。The result set is formatted as a JSON array where each row is formatted as a separate JSON object.

PATH は、列の別名にドット表記を使用して、JSON の結果の出力形式をカスタマイズできることを示します。PATH indicates that you can customize the output format of your JSON result by using dot notation in column aliases. 次のクエリでは、JSON 形式の出力で "CustomerName" キーの名前を変更し、電話番号と FAX 番号を "Contact" サブオブジェクトに入力します。The following query changes the name of the "CustomerName" key in the output JSON format, and puts phone and fax numbers in the "Contact" sub-object:

select CustomerName as Name, PhoneNumber as [Contact.Phone], FaxNumber as [Contact.Fax]
from Sales.Customers
where CustomerID = 931
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

このクエリの出力は次のようになります。The output of this query looks like this:

{
    "Name":"Nada Jovanovic",
    "Contact":{
           "Phone":"(215) 555-0100",
           "Fax":"(215) 555-0101"
    }
}

この例では、WITHOUT_ARRAY_WRAPPER オプションを指定することで、配列ではなく、1 つの JSON オブジェクトを返しています。In this example, we returned a single JSON object instead of an array by specifying the WITHOUT_ARRAY_WRAPPER option. クエリの結果として 1 つのオブジェクトを返すことがわかっている場合は、このオプションを使用することができます。You can use this option if you know that you are returning a single object as a result of query.

FOR JSON 句の主な値を使用すると、入れ子になった JSON オブジェクトまたは配列として書式設定されたデータベースから複雑な階層データを返すことができます。The main value of the FOR JSON clause is that it lets you return complex hierarchical data from your database formatted as nested JSON objects or arrays. 次の例では、Orders の入れ子になった配列として、Customer に属する Orders テーブルから行を含める方法を示します。The following example shows how to include the rows from the Orders table that belong to the Customer as a nested array of Orders:

select CustomerName as Name, PhoneNumber as Phone, FaxNumber as Fax,
        Orders.OrderID, Orders.OrderDate, Orders.ExpectedDeliveryDate
from Sales.Customers Customer
    join Sales.Orders Orders
        on Customer.CustomerID = Orders.CustomerID
where Customer.CustomerID = 931
FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER

Customer データを取得して、関連する Orders のリストを取得するために、個別のクエリを送信する代わりに、次の出力例のように、1 つのクエリで必要なデータをすべて取得することができます。Instead of sending separate queries to get Customer data and then to fetch a list of related Orders, you can get all the necessary data with a single query, as shown in the following sample output:

{
  "Name":"Nada Jovanovic",
  "Phone":"(215) 555-0100",
  "Fax":"(215) 555-0101",
  "Orders":[
    {"OrderID":382,"OrderDate":"2013-01-07","ExpectedDeliveryDate":"2013-01-08"},
    {"OrderID":395,"OrderDate":"2013-01-07","ExpectedDeliveryDate":"2013-01-08"},
    {"OrderID":1657,"OrderDate":"2013-01-31","ExpectedDeliveryDate":"2013-02-01"}
]
}

JSON データの使用Working with JSON data

厳密に構造化されたデータがない場合、複雑なサブオブジェクト、配列、または階層データがある場合、またはデータ構造が時間と共に進化する場合、JSON 形式を使うと、すべての複雑なデータ構造を表すことができます。If you don’t have strictly structured data, if you have complex sub-objects, arrays, or hierarchical data, or if your data structures evolve over time, the JSON format can help you to represent any complex data structure.

JSON は、Azure SQL Database の他の文字列型のように使用できるテキスト形式です。JSON is a textual format that can be used like any other string type in Azure SQL Database. JSON データは、標準の NVARCHAR として送信または格納することができます。You can send or store JSON data as a standard NVARCHAR:

CREATE TABLE Products (
  Id int identity primary key,
  Title nvarchar(200),
  Data nvarchar(max)
)
go
CREATE PROCEDURE InsertProduct(@title nvarchar(200), @json nvarchar(max))
AS BEGIN
    insert into Products(Title, Data)
    values(@title, @json)
END

この例で使用される JSON データは、NVARCHAR(MAX) 型を使用して表されます。The JSON data used in this example is represented by using the NVARCHAR(MAX) type. JSON は、次の例のように標準の Transact-SQL 構文を使用して、このテーブルに挿入したり、格納されたプロシージャの引数として指定したりすることができます。JSON can be inserted into this table or provided as an argument of the stored procedure using standard Transact-SQL syntax as shown in the following example:

EXEC InsertProduct 'Toy car', '{"Price":50,"Color":"White","tags":["toy","children","games"]}'

また、Azure SQL Database の文字列データを操作する任意のクライアント側の言語またはライブラリも、JSON データを操作することができます。Any client-side language or library that works with string data in Azure SQL Database will also work with JSON data. JSON は、メモリ最適化テーブルやシステム バージョン管理されたテーブルなど、NVARCHAR 型をサポートする任意のテーブルに格納できます。JSON can be stored in any table that supports the NVARCHAR type, such as a Memory-optimized table or a System-versioned table. JSON は、クライアント側のコードまたはデータベース層のいずれにも、既存の制約を導入しません。JSON does not introduce any constraint either in the client-side code or in the database layer.

JSON データをクエリするQuerying JSON data

Azure SQL テーブルに格納された JSON 形式のデータがある場合、JSON 関数では、このデータを任意の SQL クエリで使用できます。If you have data formatted as JSON stored in Azure SQL tables, JSON functions let you use this data in any SQL query.

Azure SQL データベースで使用可能な JSON 関数を使用すると、JSON 形式のデータを他の SQL データ型として処理できます。JSON functions that are available in Azure SQL database let you treat data formatted as JSON as any other SQL data type. JSON テキストから簡単に値を抽出し、任意のクエリで JSON データを使用できます。You can easily extract values from the JSON text, and use JSON data in any query:

select Id, Title, JSON_VALUE(Data, '$.Color'), JSON_QUERY(Data, '$.tags')
from Products
where JSON_VALUE(Data, '$.Color') = 'White'

update Products
set Data = JSON_MODIFY(Data, '$.Price', 60)
where Id = 1

JSON_VALUE 関数は、Data 列に格納された JSON テキストから値を抽出します。The JSON_VALUE function extracts a value from JSON text stored in the Data column. この関数では、JavaScript のようなパスを使用して、抽出する JSON テキストの値を参照します。This function uses a JavaScript-like path to reference a value in JSON text to extract. 抽出された値は、SQL クエリの任意の部分で使用できます。The extracted value can be used in any part of SQL query.

JSON_QUERY 関数は、JSON_VALUE と同様です。The JSON_QUERY function is similar to JSON_VALUE. JSON_VALUE とは異なり、この関数は JSON テキストに配置されている配列やオブジェクトなどの複雑なサブオブジェクトを抽出します。Unlike JSON_VALUE, this function extracts complex sub-object such as arrays or objects that are placed in JSON text.

JSON_MODIFY 関数は、更新する必要がある JSON テキストの値だけでなく、古い値を上書きする新しい値のパスを指定することができます。The JSON_MODIFY function lets you specify the path of the value in the JSON text that should be updated, as well as a new value that will overwrite the old one. この方法では、構造全体を再解析することなく、JSON テキストを簡単に更新できます。This way you can easily update JSON text without reparsing the entire structure.

JSON は標準テキストで格納されるため、値が適切に書式設定されたテキスト列に格納される保証はありません。Since JSON is stored in a standard text, there are no guarantees that the values stored in text columns are properly formatted. 標準の Azure SQL Database の CHECK 制約および ISJSON 関数を使用すると、JSON 列に格納されたテキストが適切に書式設定されていることを確認できます。You can verify that text stored in JSON column is properly formatted by using standard Azure SQL Database check constraints and the ISJSON function:

ALTER TABLE Products
    ADD CONSTRAINT [Data should be formatted as JSON]
        CHECK (ISJSON(Data) > 0)

入力テキストが適切な JSON 形式である場合、ISJSON 関数は値 1 を返します。If the input text is properly formatted JSON, the ISJSON function returns the value 1. JSON 列を挿入または更新するたびに、この制約は新しいテキスト値が無効な形式の JSON ではないことを確認します。On every insert or update of JSON column, this constraint will verify that new text value is not malformed JSON.

JSON を表形式に変換するTransforming JSON into tabular format

Azure SQL Database では、JSON コレクションを表形式の書式設定に変換し、JSON データの読み込みまたはクエリを行うこともできます。Azure SQL Database also lets you transform JSON collections into tabular format and load or query JSON data.

OPENJSON は、テーブル値関数です。この関数は、JSON テキストの解析、JSON オブジェクトの配列の検索、配列の要素の反復処理、および配列の各要素の出力結果に 1 行を返す操作を行うことができます。OPENJSON is a table-value function that parses JSON text, locates an array of JSON objects, iterates through the elements of the array, and returns one row in the output result for each element of the array.

JSON 表形式

上記の例では、開く必要がある JSON 配列を検索する場所 ($.Orders パス)、結果として返される列、およびセルとして返される JSON 値を検索する場所を指定できます。In the example above, we can specify where to locate the JSON array that should be opened (in the $.Orders path), what columns should be returned as result, and where to find the JSON values that will be returned as cells.

@orders 変数の JSON 配列を行セットに変換したり、この結果セットを分析したり、標準テーブルに行を挿入したりすることができます。We can transform a JSON array in the @orders variable into a set of rows, analyze this result set, or insert rows into a standard table:

CREATE PROCEDURE InsertOrders(@orders nvarchar(max))
AS BEGIN

    insert into Orders(Number, Date, Customer, Quantity)
    select Number, Date, Customer, Quantity
    FROM OPENJSON (@orders)
     WITH (
            Number varchar(200),
            Date datetime,
            Customer varchar(200),
            Quantity int
     )

END

JSON 配列として書式設定され、ストアド プロシージャにパラメーターとして指定される orders のコレクションは、解析され、Orders テーブルに挿入することができます。The collection of orders formatted as a JSON array and provided as a parameter to the stored procedure can be parsed and inserted into the Orders table.

次の手順Next steps

アプリケーションに JSON を統合する方法については、次のリソースを確認してください。To learn how to integrate JSON into your application, check out these resources:

JSON をアプリケーションに統合するためのさまざまなシナリオの詳細については、この Channel 9 のビデオのデモを参照するか、JSON のブログの投稿でお客様のユース ケースに一致するシナリオを見つけてください。To learn about various scenarios for integrating JSON into your application, see the demos in this Channel 9 video or find a scenario that matches your use case in JSON Blog posts.