OPENJSON で JSON データを解析し、変換する
適用対象: 
SQL Server 2016 (13.x) 以降 Azure SQL データベース Azure SQL Managed Instance Azure Synapse Analytics
OPENJSON 行セット関数は、JSON TEXT を ROWS と COLUMN のセットに変換します。 OPENJSON を使用して JSON コレクションを行セットに変換したら、返されたデータで SQL クエリを実行したり、行セットを SQL Server テーブルに挿入したりできます。 SQL Server データベース エンジンでの JSON データの操作の詳細については、「SQL Server の JSON データ」を参照してください。
OPENJSON 関数は、1 つまたは集合の JSON オブジェクトを受け取り、1 つまたは複数の ROWS に変換します。 既定では、OPENJSON 関数が次のデータを返します。
- JSON オブジェクトからは、最初のレベルで検出されたすべてのキーと値のペアを返します。
- JSON 配列からは、配列のすべての要素とそれらのインデックスを返します。
オプションの WITH 句を追加して、出力の構造を明示的に定義するスキーマを指定できます。
デフォルト出力を指定した OPENJSON
結果の明示的なスキーマ (つまり WITH の後の OPENJSON 句) を指定せずに OPENJSON 関数を使用すると、次の 3 つの COLUMN を含むテーブルを返します。
- 入力オブジェクト (または入力配列の要素のインデックス) のプロパティの
name。 - プロパティまたは配列要素の
value。 type(たとえば、文字列、数値、ブール値、ARRAY、オブジェクト)。
OPENJSON は JSON オブジェクトの各プロパティ、または ARRAY の各要素を個別の ROW として返します。
これは、デフォルトスキーマ (つまり、オプションの WITH 句を指定しない) で OPENJSON を使用した簡単な例です。この例では JSON オブジェクトのプロパティごとに 1 つの ROW が返されています。
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 |
age |
45 |
2 |
skills |
[ "SQL" ,"C#" ,"MVC" ] |
4 |
詳細と例については、「デフォルトスキーマで OPENJSON を使用する」を参照してください。
構文と使用法については、「OPENJSON」をご覧ください。
明示的な構造を指定した OPENJSON 出力
OPENJSON 関数の WITH 句を使用して結果のスキーマを指定すると、WITH 句に定義した COLUMN のみを持つテーブルを返します。 オプションの WITH 句では、いくつかの出力列、各 COLUMN の型、各出力 VALUE の JSON ソース プロパティのパスを指定します。 OPENJSON は JSON オブジェクトの ARRAY を繰り返し処理し、COLUMN ごとに指定されたパスで VALUE を読み取り、VALUE を指定の TYPE に変換します。
次の例では、OPENJSON 句で明示的に指定した出力のスキーマで WITH を使用します。
DECLARE @json NVARCHAR(MAX);
SET @json = N'[
{
"Order": {
"Number": "SO43659",
"Date": "2024-05-31T00:00:00"
},
"AccountNumber": "AW29825",
"Item": {
"Price": 2024.9940,
"Quantity": 1
}
},
{
"Order": {
"Number": "SO43661",
"Date": "2024-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 |
2024-05-31T00:00:00 |
AW29825 |
1 |
SO43661 |
2024-06-01T00:00:00 |
AW73565 |
3 |
この関数は JSON 配列の要素を返し、書式設定します。
JSON ARRAY の要素ごとに、
OPENJSONによって出力テーブルに新しい ROW が生成されます。 JSON 配列の 2 つの要素が、返されるテーブルで、2 つの行に変換されます。colName type json_path構文を使用して指定された COLUMN ごとに、OPENJSONは指定されたパスの配列要素で検出された VALUE を指定の TYPE に変換します。 この例では、Date列の値がパス$.Order.Dateの各要素から取得され、datetime 値に変換されます。
詳細と例については、「明示的なスキーマで OPENJSON を使用する (SQL Server)」を参照してください。
構文と使用法については、「OPENJSON」をご覧ください。
OPENJSON には、互換性レベル 130 が必要です。
OPENJSON 関数は、 互換性レベル 130 以上でのみ使用できます。 データベース互換レベルが 130 よりも低い場合、SQL Server は OPENJSON 関数を見つけて実行することができません。 他の組込み JSON 関数は、すべての互換性レベルで使用できます。
次のコマンドを使用して、sys.databases ビューまたはデータベースのプロパティで互換性レベルをチェックし、データベースの互換性レベルを変更できます。
ALTER DATABASE <DatabaseName> SET COMPATIBILITY_LEVEL = 130;