OPENJSON を使用して JSON データを解析して変換する (SQL Server)Parse and Transform JSON Data with OPENJSON (SQL Server)

適用対象: ○SQL Server ○Azure SQL Database ○Azure SQL Data Warehouse XParallel Data WarehouseAPPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse noParallel Data Warehouse

OPENJSON 行セット関数は、JSON テキストを行と列のセットに変換します。The OPENJSON rowset function converts JSON text into a set of rows and columns. OPENJSON を使用して JSON コレクションを行セットに変換したら、返されたデータで SQL クエリを実行したり、行セットをテーブルに挿入したりできます。After you transform a JSON collection into a rowset with OPENJSON, you can run any SQL query on the returned data or insert it into a SQL Server table.

OPENJSON 関数は、1 つまたは集合の JSON オブジェクトを受け取り、1 つまたは複数の行に変換します。The OPENJSON function takes a single JSON object or a collection of JSON objects and transforms them into one or more rows. 既定では、OPENJSON 関数からは、次のデータが返されます。By default, the OPENJSON function returns the following data:

  • JSON オブジェクトからは、最初のレベルで検出されたすべてのキーと値のペアを返します。From a JSON object, the function returns all the key/value pairs that it finds at the first level.
  • JSON 配列からは、配列のすべての要素とそれらのインデックスを返します。From a JSON array, the function returns all the elements of the array with their indexes.

オプションの WITH 句を追加して、出力の構造を明示的に定義するスキーマを指定できます。You can add an optional WITH clause to provide a schema that explicitly defines the structure of the output.

オプション 1 - 既定の出力を使用する OPENJSONOption 1 - OPENJSON with the default output

結果の明示的なスキーマ (つまり OPENJSON の後の WITH 句) を指定せずに OPENJSON 関数を使用すると、関数は、次の 3 つの列を含むテーブルを返します。When you use the OPENJSON function without providing an explicit schema for the results - that is, without a WITH clause after OPENJSON - the function returns a table with the following three columns:

  1. 入力オブジェクトのプロパティの名前 (または入力配列の要素のインデックス)。The name of the property in the input object (or the index of the element in the input array).
  2. プロパティまたは配列要素のThe value of the property or the array element.
  3. (たとえば、文字列、数値、ブール値、配列、オブジェクト)。The type (for example, string, number, boolean, array, or object).

OPENJSON は、JSON オブジェクトの各プロパティ、または配列の各要素を、個別の行として返します。OPENJSON returns each property of the JSON object, or each element of the array, as a separate row.

これは、既定のスキーマ (つまり、オプションの WITH 句を指定しない) で OPENJSON を使用した簡単な例です。この例では JSON オブジェクトのプロパティごとに 1 つの行が返されています。Here's a quick example that uses OPENJSON with the default schema - that is, without the optional WITH clause - and returns one row for each property of the JSON object.

Example

DECLARE @json NVARCHAR(MAX)

SET @json='{"name":"John","surname":"Doe","age":45,"skills":["SQL","C#","MVC"]}';

SELECT *
FROM OPENJSON(@json);

結果Results

キー (key)key valuevalue type
NAMEname JohnJohn 11
surname DoeDoe 11
有効期間age 4545 22
スキルskills ["SQL","C#","MVC"]["SQL","C#","MVC"] 44

既定のスキーマを使用する OPENJSON に関する詳細情報More info about OPENJSON with the default schema

詳細と例については、「既定のスキーマを使用する OPENJSON の使用 (SQL Server)」を参照してください。For more info and examples, see Use OPENJSON with the Default Schema (SQL Server).

構文と使用法については、「 OPENJSON (Transact-SQL)でのみ使用できます。For syntax and usage, see OPENJSON (Transact-SQL).

オプション 2 - 明示的な構造を指定した OPENJSON 出力Option 2 - OPENJSON output with an explicit structure

OPENJSON 関数の WITH 句を使用して結果のスキーマを指定すると、WITH 句に定義した列のみを持つテーブルが返されます。When you specify a schema for the results by using the WITH clause of the OPENJSON function, the function returns a table with only the columns that you define in the WITH clause. オプションの WITH 句では、いくつかの出力列、各列の型、各出力値の JSON ソース プロパティのパスを指定します。In the optional WITH clause, you specify a set of output columns, their types, and the paths of the JSON source properties for each output value. OPENJSON は JSON オブジェクトの配列を繰り返し処理し、列ごとに指定されたパスで値を読み取り、値を指定の型に変換します。OPENJSON iterates through the array of JSON objects, reads the value on the specified path for each column, and converts the value to the specified type.

WITH 句で出力のスキーマを明示的に指定して OPENJSON を使用する簡単な例は次のようになります。Here's a quick example that uses OPENJSON with a schema for the output that you explicitly specify in the WITH clause.

Example

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'  
 ) 

結果Results

数値Number dateDate CustomerCustomer QuantityQuantity
SO43659SO43659 2011-05-により、2011-05-31T00:00:00 AW29825AW29825 11
として SO43661SO43661 2011-06-01T00:00:002011-06-01T00:00:00 AW73565AW73565 33

この関数は JSON 配列の要素を返し、書式設定します。This function returns and formats the elements of a JSON array.

  • JSON 配列の要素ごとに、 OPENJSON によって出力テーブルに新しい行が生成されます。For each element in the JSON array, OPENJSON generates a new row in the output table. JSON 配列の 2 つの要素が、返されるテーブルで、2 つの行に変換されます。The two elements in the JSON array are converted into two rows in the returned table.

  • colName type json_path 構文を使用して指定された列ごとに、OPENJSON 関数は、指定したパスの配列要素で検出された値を指定の型に変換します。For each column, specified by using the colName type json_path syntax, OPENJSON converts the value found in each array element on the specified path to the specified type. この例では、Date 列の値がパス $.Order.Date の各要素から取得され、datetime 値に変換されます。In this example, values for the Date column are taken from each element on the path $.Order.Date and converted to datetime values.

明示的なスキーマを指定した OPENJSON に関する詳細情報More info about OPENJSON with an explicit schema

詳細と例については、「明示的なスキーマで OPENJSON を使用する (SQL Server)」を参照してください。For more info and examples, see Use OPENJSON with an Explicit Schema (SQL Server).

構文と使用法については、「 OPENJSON (Transact-SQL)でのみ使用できます。For syntax and usage, see OPENJSON (Transact-SQL).

OPENJSON には、互換性レベル 130 が必要OPENJSON requires Compatibility Level 130

OPENJSON 関数は、 互換性レベル 130でのみ使用できます。The OPENJSON function is available only under compatibility level 130. データベースの互換性レベルが 130 よりも低い場合、SQL Server は OPENJSON 関数を見つけて実行することができません。If your database compatibility level is lower than 130, SQL Server can't find and run the OPENJSON function. 他の組込み JSON 関数は、すべての互換性レベルで使用できます。Other built-in JSON functions are available at all compatibility levels.

sys.databases ビューまたはデータベース プロパティで互換性レベルを確認できます。You can check compatibility level in the sys.databases view or in database properties.

次のコマンドを利用し、データベースの互換性レベルを変更できます。You can change the compatibility level of a database by using the following command:
ALTER DATABASE <DatabaseName> SET COMPATIBILITY_LEVEL = 130

SQL Server と Azure SQL Database の JSON の詳細情報Learn more about JSON in SQL Server and Azure SQL Database

Microsoft ビデオMicrosoft videos

SQL Server と Azure SQL Database に組み込まれている JSON のサポートの視覚的な紹介は、次のビデオをご覧ください。For a visual introduction to the built-in JSON support in SQL Server and Azure SQL Database, see the following videos:

参照See Also

OPENJSON (Transact-SQL)OPENJSON (Transact-SQL)