適用対象:
Azure SQL DatabaseAzure SQL Managed InstanceMicrosoft Fabric SQL Database
Azure SQL Database、Microsoft Fabric の SQL データベース、Azure SQL Managed Instance を使用すると、JavaScript Object Notation (JSON) 形式で表されるデータを解析してクエリを実行し、リレーショナル データを JSON テキストとしてエクスポートできます。 次の JSON シナリオを使用できます。
-
句を使用した
FOR JSON。 - JSON データの使用
- JSON スカラー関数を使用した JSON データのクエリの実行。
-
関数を使用した
OPENJSON。
リレーショナル データを JSON 形式で書式設定する
データベース層からデータを取得し、JSON 形式で応答を提供する Web サービスがある場合、またはクライアント側の JavaScript フレームワークまたはライブラリが JSON 形式のデータを受け入れる場合、SQL クエリに直接 JSON としてデータベースの内容を書式設定することができます。 結果を JSON として書式設定するアプリケーション コードを記述したり、表形式のクエリ結果を変換してオブジェクトを 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 テキストとして書式設定します。 次のように、セルの値が 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 結果の出力形式をカスタマイズできることを示します。 次のクエリでは、 CustomerName キーの名前が出力 JSON 形式で変更され、 Contact サブオブジェクトに電話番号と FAX 番号が格納されます。
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
別のクエリを送信して Customer データを取得し、関連する 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 は、他の文字列型と同様に使用できるテキスト形式です。 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"]}'
文字列データを操作するクライアント側の任意の言語またはライブラリでも、JSON データを操作できます。 JSON は、メモリ最適化テーブルやシステム バージョン管理テーブルなど、nvarchar 型をサポートする任意のテーブルに格納できます。 JSON は、クライアント側のコードまたはデータベース層のいずれにも、既存の制約を導入しません。
JSON のデータのクエリを実行する
JSON 形式のデータがテーブルに格納されている場合、JSON 関数を使用すると、任意の SQL クエリでこのデータを使用できます。
JSON 関数を使用すると、JSON 形式のデータを他の SQL データ型として扱うことができます。 JSON テキストから簡単に値を抽出し、任意のクエリで 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関数は、Data 列に格納されている JSON テキストから値を抽出します。 この関数では、JavaScript のようなパスを使用して、抽出する JSON テキストの値を参照します。 抽出された値は、SQL クエリの任意の部分で使用できます。
JSON_QUERY関数は、JSON_VALUEに似ています。
JSON_VALUEとは異なり、この関数は、JSON テキストに配置された配列やオブジェクトなどの複雑なサブオブジェクトを抽出します。
JSON_MODIFY関数を使用すると、更新する必要がある JSON テキスト内の値のパスと、古い値を上書きする新しい値を指定できます。 この方法では、構造全体を再解析することなく、JSON テキストを簡単に更新できます。
JSON は標準テキストで格納されるため、値が適切に書式設定されたテキスト列に格納される保証はありません。 標準の CHECK 制約と ISJSON 関数を使用すると、JSON 列に格納されたテキストが適切に書式設定されていることを確認できます。
ALTER TABLE Products
ADD CONSTRAINT [Data should be formatted as JSON]
CHECK (ISJSON(Data) > 0)
入力テキストが適切に書式設定された JSON の場合、 ISJSON 関数は 1値を返します。 JSON 列を挿入または更新するたびに、この制約は新しいテキスト値が無効な形式の JSON ではないことを確認します。
JSON を表形式に変換する
JSON コレクションを表形式に変換し、JSON データの読み込みまたはクエリを行うこともできます。
OPENJSON は、JSON テキストを解析し、JSON オブジェクトの配列を検索し、配列の要素を反復処理し、配列の要素ごとに出力結果の 1 行を返すテーブル値 T-SQL 関数です。
この例では、開く必要がある JSON 配列の場所 ( $.Orders パス内)、結果として返される列、およびセルとして返される 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 テーブルに挿入することができます。