Azure SQL データベース と Azure SQL Managed Instance の JSON 機能の概要
適用対象:
Azure SQL DatabaseAzure SQL Managed Instance
Azure SQL データベース と Azure SQL Managed Instance では、JavaScript Object Notation (JSON) 形式で表されたデータを解析および照会し、関連 データを JSON TEXTとしてエクスポートすることができます。 次の JSON シナリオを使用できます。
FOR JSON句を使用した JSON 形式での関連 データの書式設定。- JSON データの使用
- JSON スカラー関数を使用した JSON データのクエリ実行。
OPENJSON関数を使用した表形式への JSON の変換。
JSON 形式での関連 データの書式設定
データベースレイヤーからデータを取得し、JSON 形式で応答を提供する Web サービスがある場合、またはクライアント側の JavaScript フレームワークまたはライブラリが JSONとしてのデータ形式を受け入れる場合、SQL クエリに直接 JSON としてデータベースの内容を書式設定することができます。 Azure SQL データベース または Azure SQL Managed Instance からの結果を JSONとして 形式化するように、アプリケーション CODEを書き込みしたり、表形式のクエリの結果に変換して、オブジェクトを JSON 形式にシリアル化するために JSON シリアル化ライブラリを含めたりする必要がなくなりました。 代わりに、FOR JSON 句を使用して、SQL クエリの結果を JSON として書式設定し、アプリケーションで直接これを使用できます。
次の例では、FOR JSON 句を使用して、Sales.Customer テーブルの行が JSON として書式設定されます。
select CustomerName, PhoneNumber, FaxNumber
from Sales.Customers
FOR JSON PATH
FOR JSON PATH 句は、クエリの結果を JSON TEXTとして書式設定します。 次のように、CELLの値が JSON 値として生成される場合、レ列名はキーとして使用されます。
[
{"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 オブジェクトとして書式設定されます。
PATH は、列の別名にドット表記を使用して、JSON の結果の出力形式をカスタマイズできることを示します。 次のクエリでは、JSON 形式の出力で "CustomerName" キーの名前を変更し、電話番号と FAX 番号を "Contact" サブオブジェクトに入力します。
select CustomerName as Name, PhoneNumber as [Contact.Phone], FaxNumber as [Contact.Fax]
from Sales.Customers
where CustomerID = 931
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
このクエリの出力は次のようになります。
{
"Name":"Nada Jovanovic",
"Contact":{
"Phone":"(215) 555-0100",
"Fax":"(215) 555-0101"
}
}
この例では、WITHOUT_ARRAY_WRAPPER オプションを指定することで、配列ではなく、1 つの JSON オブジェクトを返しています。 クエリの結果として 1 つのオブジェクトを返すことがわかっている場合は、このオプションを使用することができます。
FOR JSON 句の主な値を使用すると、入れ子になった JSON オブジェクトまたは配列として書式設定されたデータベースから複雑な階層データを返すことができます。 次の例では、Orders の入れ子になった配列として、Customer に属する 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
顧客データを取得して、関連する Orders のリストをフェッチするために、個別のクエリを送信する代わりに、次の出力例のように、1 つのクエリで必要なデータをすべて取得することができます。
{
"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 データの使用
厳密に構造化されたデータがない場合、複雑なサブオブジェクト、配列、または階層データがある場合、またはデータ構造が時間と共に進化する場合、JSON 形式を使うと、すべての複雑なデータ構造を表すことができます。
JSON は、Azure SQL データベース および Azure SQL Managed Instance の他の文字列型と同様に使用できるテキスト形式です。 JSON データは、標準の 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) 型を使用して表されます。 JSON は、次の例のように標準の Transact-SQL 構文を使用して、このテーブルに挿入したり、ストアド プロシージャの引数として指定したりすることができます。
EXEC InsertProduct 'Toy car', '{"Price":50,"Color":"White","tags":["toy","children","games"]}'
また、Azure SQL データベース および Azure SQL Managed Instance の文字列データを操作する任意のクライアント側の言語またはライブラリも、JSON データを操作することができます。 JSON は、メモリ最適化テーブルやシステム バージョン化されたテーブルなど、NVARCHAR 型をサポートする任意のテーブルに格納できます。 JSON は、クライアント側のCODEまたはデータベースレイヤーのいずれにも、任意の制約を導入しません。
JSON データをクエリする
Azure SQL テーブルに格納された JSON としての形式化されたデータがある場合、JSON 関数では、このデータを任意の SQL クエリで使用できます。
Azure SQL データベース および Azure SQL Managed Instance で使用可能な JSON 関数を使用すると、JSONとしての形式化されたデータを他の SQL データ型として処理できます。 JSON TEXTから簡単に値を抽出し、任意のクエリで JSON データを使用できます。
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 FUNCTIONは、Data 列に格納された JSON TEXTから値を抽出します。 この関数では、JavaScript のようなパスを使用して、抽出する JSON TEXTの値を参照します。 抽出された値は、SQL クエリの任意のパーツで使用できます。
JSON_QUERY FUNCTIONは、JSON_VALUE と同様です。 JSON_VALUE とは異なり、この関数は JSON TEXTに配置されている配列やオブジェクトなどの複雑なサブオブジェクトを抽出します。
JSON_MODIFY 関数は、更新する必要がある JSON TEXTのV値だけでなく、古い値を上書きする新しい値のパスを指定することができます。 この方法では、構造全体を再解析することなく、JSON TEXTを簡単に更新できます。
JSON は標準TEXTで格納されるため、値が適切に書式設定されたテキスト列に格納される保証はありません。 標準の Azure SQL データベース の CHECK 制約および ISJSON 関数を使用すると、JSON 列に格納されたテキストが適切に書式設定されていることを確認できます。
ALTER TABLE Products
ADD CONSTRAINT [Data should be formatted as JSON]
CHECK (ISJSON(Data) > 0)
入力TEXTが適切な JSON 形式である場合、ISJSON 関数は値 1 を返します。 JSON 列を挿入または更新するたびに、この制約は新しいテキスト値が無効な形式の JSON ではないことを確認します。
JSON を表形式に変換する
Azure SQL データベース および Azure SQL Managed Instance では、JSON コレクションを表形式の書式設定に変換し、JSON データの読み込みまたはクエリを行うこともできます。
OPENJSON は、テーブル値関数です。この関数は、JSON TEXTの解析、JSON オブジェクトの配列の検索、配列の要素の反復処理、および配列の各要素の出力結果に 1 行を返す操作を行うことができます。
上記の例では、開く必要がある JSON 配列を検索する場所 ($.Orders パス)、結果として返される列、およびCELLとして返される JSON 値を検索する場所を指定できます。
@orders 変数の JSON 配列を行セットに変換したり、この結果セットを分析したり、標準テーブルに行を挿入したりすることができます。
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 テーブルに挿入することができます。
フィードバック
以下は間もなく提供いたします。2024 年を通じて、コンテンツのフィードバック メカニズムとして GitHub の issue を段階的に廃止し、新しいフィードバック システムに置き換えます。 詳細については、「https://aka.ms/ContentUserFeedback」を参照してください。
フィードバックの送信と表示