OPENJSON で JSON データを解析し、変換する
適用対象: SQL Server 2016 (13.x) 以降 Azure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics
OPENJSON 行セット関数は、JSON テキストを行と列のセットに変換します。 OPENJSON を使用して JSON コレクションを行セットに変換したら、返されたデータで SQL クエリを実行したり、行セットをテーブルに挿入したりできます。
OPENJSON 関数は、1 つまたは集合の JSON オブジェクトを受け取り、1 つまたは複数の行に変換します。 既定では、OPENJSON 関数からは、次のデータが返されます。
- JSON オブジェクトからは、最初のレベルで検出されたすべてのキーと値のペアを返します。
- JSON 配列からは、配列のすべての要素とそれらのインデックスを返します。
オプションの WITH 句を追加して、出力の構造を明示的に定義するスキーマを指定できます。
オプション 1 - 既定の出力を使用する OPENJSON
結果の明示的なスキーマ (つまり OPENJSON の後の WITH 句) を指定せずに OPENJSON 関数を使用すると、関数は、次の 3 つの列を含むテーブルを返します。
- 入力オブジェクトのプロパティの名前 (または入力配列の要素のインデックス)。
- プロパティまたは配列要素の値。
- 型 (たとえば、文字列、数値、ブール値、配列、オブジェクト)。
OPENJSON は、JSON オブジェクトの各プロパティ、または配列の各要素を、個別の行として返します。
これは、既定のスキーマ (つまり、オプションの WITH 句を指定しない) で OPENJSON を使用した簡単な例です。この例では JSON オブジェクトのプロパティごとに 1 つの行が返されています。
例:
DECLARE @json NVARCHAR(MAX)
SET @json='{"name":"John","surname":"Doe","age":45,"skills":["SQL","C#","MVC"]}';
SELECT *
FROM OPENJSON(@json);
結果:
key | 値 | type |
---|---|---|
name | John | 1 |
surname | Doe | 1 |
年齢 | 45 | 2 |
スキル | ["SQL","C#","MVC"] | 4 |
既定のスキーマを使用する OPENJSON に関する詳細情報
詳細と例については、「既定のスキーマを使用する OPENJSON の使用 (SQL Server)」を参照してください。
構文と使用法については、「OPENJSON (Transact-SQL)をご覧ください。
オプション 2 - 明示的な構造を指定した OPENJSON 出力
OPENJSON 関数の WITH 句を使用して結果のスキーマを指定すると、WITH 句に定義した列のみを持つテーブルが返されます。 オプションの WITH 句では、いくつかの出力列、各列の型、各出力値の JSON ソース プロパティのパスを指定します。 OPENJSON は JSON オブジェクトの配列を繰り返し処理し、列ごとに指定されたパスで値を読み取り、値を指定の型に変換します。
WITH 句で出力のスキーマを明示的に指定して OPENJSON を使用する簡単な例は次のようになります。
例:
DECLARE @json NVARCHAR(MAX)
SET @json =
N'[
{
"Order": {
"Number":"SO43659",
"Date":"2011-05-31T00:00:00"
},
"AccountNumber":"AW29825",
"Item": {
"Price":2024.9940,
"Quantity":1
}
},
{
"Order": {
"Number":"SO43661",
"Date":"2011-06-01T00:00:00"
},
"AccountNumber":"AW73565",
"Item": {
"Price":2024.9940,
"Quantity":3
}
}
]'
SELECT * FROM
OPENJSON ( @json )
WITH (
Number varchar(200) '$.Order.Number' ,
Date datetime '$.Order.Date',
Customer varchar(200) '$.AccountNumber',
Quantity int '$.Item.Quantity'
)
結果:
番号 | Date | Customer | Quantity |
---|---|---|---|
SO43659 | 2011-05-により、 | AW29825 | 1 |
として SO43661 | 2011-06-01T00:00:00 | AW73565 | 3 |
この関数は JSON 配列の要素を返し、書式設定します。
JSON 配列の要素ごとに、 OPENJSON によって出力テーブルに新しい行が生成されます。 JSON 配列の 2 つの要素が、返されるテーブルで、2 つの行に変換されます。
colName type json_path
構文を使用して指定された列ごとに、OPENJSON 関数は、指定したパスの配列要素で検出された値を指定の型に変換します。 この例では、Date
列の値がパス$.Order.Date
の各要素から取得され、datetime 値に変換されます。
明示的なスキーマを指定した OPENJSON に関する詳細情報
詳細と例については、「明示的なスキーマで OPENJSON を使用する (SQL Server)」を参照してください。
構文と使用法については、「OPENJSON (Transact-SQL)をご覧ください。
OPENJSON には、互換性レベル 130 が必要
OPENJSON 関数は、 互換性レベル 130でのみ使用できます。 データベースの互換性レベルが 130 よりも低い場合、SQL Server は OPENJSON 関数を見つけて実行することができません。 他の組込み JSON 関数は、すべての互換性レベルで使用できます。
sys.databases
ビューまたはデータベース プロパティで互換性レベルを確認できます。
次のコマンドを利用し、データベースの互換性レベルを変更できます。
ALTER DATABASE <DatabaseName> SET COMPATIBILITY_LEVEL = 130
SQL Server と Azure SQL Database の JSON の詳細情報
Microsoft ビデオ
Note
このセクションのビデオ リンクの一部は、現時点では機能しない場合があります。 Microsoft では、以前 Channel 9 上にあったコンテンツの新しいプラットフォームへの移行作業を進めています。 ビデオが新しいプラットフォームに移行されるに従ってリンクを更新します。
SQL Server と Azure SQL Database に組み込まれている JSON のサポートの視覚的な紹介は、次のビデオをご覧ください。
関連項目
フィードバック
https://aka.ms/ContentUserFeedback」を参照してください。
以下は間もなく提供いたします。2024 年を通じて、コンテンツのフィードバック メカニズムとして GitHub の issue を段階的に廃止し、新しいフィードバック システムに置き換えます。 詳細については、「フィードバックの送信と表示