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

このトピックの対象: ○SQL Server (2016 以降)○Azure SQL Database×Azure SQL Data Warehouse ×Parallel Data Warehouse THIS TOPIC APPLIES TO: yesSQL Server (starting with 2016)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

OPENJSON は、JSON テキストを解析し、JSON 入力からのオブジェクトとプロパティを行と列として返すテーブル値関数です。OPENJSON is a table-valued function that parses JSON text and returns objects and properties from the JSON input as rows and columns. つまり、OPENJSON は、JSON ドキュメントに対する行セット ビューを提供します。In other words, OPENJSON provides a rowset view over a JSON document. 行セット内の列と、それらの列に入力するために使用する JSON プロパティのパスを明示的に指定できます。You can explicitly specify the columns in the rowset and the JSON property paths used to populate the columns. OPENJSON は一連の行を返すため、他のテーブル、ビュー、またはテーブル値関数で使用するのと同じように、OPENJSONTransact-SQLTransact-SQL ステートメントの FROM 句で使用できます。Since OPENJSON returns a set of rows, you can use OPENJSON in the FROM clause of a Transact-SQLTransact-SQL statement just as you can use any other table, view, or table-valued function.

OPENJSON を使用して JSON データを SQL ServerSQL Server にインポートするか、JSON を直接使用できないアプリまたはサービスのために JSON データをリレーショナル形式に変換できます。Use OPENJSON to import JSON data into SQL ServerSQL Server, or to convert JSON data to relational format for an app or service that can't consume JSON directly.

注意

OPENJSON 関数は、互換性レベル 130 以上でのみ使用できます。The OPENJSON function is available only under compatibility level 130 or higher. データベースの互換性レベルが 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 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 with the following command:

ALTER DATABASE DatabaseName SET COMPATIBILITY_LEVEL = 130

新しい Azure SQL データベースであっても、互換性レベル 120 が既定値の場合があります。Compatibility level 120 may be the default even in a new Azure SQL Database.

トピック リンク アイコンTransact-SQL 構文表記規則Topic link iconTransact-SQL Syntax Conventions

構文Syntax

OPENJSON( jsonExpression [ , path ] )  [ <with_clause> ]

<with_clause> ::= WITH ( { colName type [ column_path ] [ AS JSON ] } [ ,...n ] )

OPENJSON テーブル値関数は、最初の引数として指定された jsonExpression を解析し、式内の JSON オブジェクトからのデータを含む 1 つまたは複数の行を返します。The OPENJSON table-valued function parses the jsonExpression provided as the first argument and returns one or more rows containing data from the JSON objects in the expression. jsonExpression には、入れ子になったサブオブジェクトを含めることができます。jsonExpression can contain nested sub-objects. jsonExpression 内からサブオブジェクトを解析する場合は、JSON サブオブジェクトの path パラメーターを指定できます。If you want to parse a sub-object from within jsonExpression, you can specify a path parameter for the JSON sub-object.

openjsonopenjson

OPENJSON TVF の構文Syntax for OPENJSON TVF

既定では、OPENJSON テーブル値関数は、jsonExpression で見つかった各 {key:value} ペアのキー名、値、および型を含む 3 つの列を返します。By default, the OPENJSON table-valued function returns three columns, which contain the key name, the value, and the type of each {key:value} pair found in jsonExpression. 別の方法として、with_clause を指定することで、OPENJSON が返す結果セットのスキーマを明示的に指定できます。As an alternative, you can explicitly specify the schema of the result set that OPENJSON returns by providing with_clause.

with_clausewith_clause

OPENJSON TVF 内の WITH 句の構文Syntax for WITH clause in OPENJSON TVF

with_clause には、OPENJSON が返す列とそれらの型の一覧が含まれます。with_clause contains a list of columns with their types for OPENJSON to return. 既定では、OPENJSON は、with_clause に指定された列名を持つ jsonExpression 内のキーと照合されます ( (この場合のキーの一致は、大文字と小文字の区別があるという意味を含みます)。By default, OPENJSON matches keys in jsonExpression with the column names in with_clause (in this case, matches keys implies that it is case sensitive). 列名がキー名と一致しない場合は、省略可能な column_path を指定できます。これは jsonExpression 内のキーを参照する JSON パス式 です。If a column name does not match a key name, you can provide an optional column_path, which is a JSON Path Expression that references a key within the jsonExpression.

引数Arguments

jsonExpressionjsonExpression

JSON テキストを含む Unicode 文字式です。Is a Unicode character expression containing JSON text.

OPENJSON では、配列の要素または JSON 式内のオブジェクトのプロパティを反復処理し、各要素またはプロパティの 1 つの行を返します。OPENJSON iterates over the elements of the array or the properties of the object in the JSON expression and returns one row for each element or property. 次の例では、jsonExpression として指定されたオブジェクトの各プロパティを返します。The following example returns each property of the object provided as jsonExpression:

DECLARE @json NVARCHAR(4000) = N'{  
   "StringValue":"John",  
   "IntValue":45,  
   "TrueValue":true,  
   "FalseValue":false,  
   "NullValue":null,  
   "ArrayValue":["a","r","r","a","y"],  
   "ObjectValue":{"obj":"ect"}  
}'

SELECT *
FROM OPENJSON(@json)

結果Results

キー (key)key valuevalue type
StringValueStringValue JohnJohn 11
IntValueIntValue 4545 22
TrueValueTrueValue truetrue 33
FalseValueFalseValue オプションfalse 33
NullValueNullValue NULLNULL 00
ArrayValueArrayValue ["a"、"r"、"r"、"「,」y"]["a","r","r","a","y"] 44
ObjectValueObjectValue {"obj":"ect"}{"obj":"ect"} 55

pathpath

jsonExpression 内のオブジェクトまたは配列を参照する省略可能な JSON パス式です。Is an optional JSON path expression that references an object or an array within jsonExpression. OPENJSON は、指定された位置で JSON テキストをシークし、参照先のフラグメントのみを解析します。OPENJSON seeks into the JSON text at the specified position and parses only the referenced fragment. 詳細については、「JSON パス式 (SQL Server)」を参照してください。For more info, see JSON Path Expressions (SQL Server).

SQL Server 2017 (14.x)SQL Server 2017 (14.x)Azure SQL データベースAzure SQL Database では、path の値として変数を指定できます。In SQL Server 2017 (14.x)SQL Server 2017 (14.x) and in Azure SQL データベースAzure SQL Database, you can provide a variable as the value of path.

次の例では、path を指定することで、入れ子になったオブジェクトを返します。The following example returns a nested object by specifying the path:

DECLARE @json NVARCHAR(4000) = N'{  
      "path": {  
            "to":{  
                 "sub-object":["en-GB", "en-UK","de-AT","es-AR","sr-Cyrl"]  
                 }  
              }  
 }';

SELECT [key], value
FROM OPENJSON(@json,'$.path.to."sub-object"')

結果Results

KeyKey ReplTest1Value
00 en-GBen-GB
11 en 英国en-UK
22 de ATde-AT
33 es ARes-AR
44 sr というsr-Cyrl

OPENJSON が JSON 配列を解析するとき、この関数は、JSON テキスト内の要素のインデックスをキーとして返します。When OPENJSON parses a JSON array, the function returns the indexes of the elements in the JSON text as keys.

JSON の式のプロパティを持つパスの手順を照合に使用する比較では、大文字と小文字および照合順序に関係なく (つまり、BIN2 の比較)。The comparison used to match path steps with the properties of the JSON expression is case-sensitive and collation-unaware (that is, a BIN2 comparison).

with_clausewith_clause

OPENJSON 関数が返す出力スキーマを明示的に定義します。Explicitly defines the output schema for the OPENJSON function to return. 省略可能な with_clause には、次の要素を含めることができます。The optional with_clause can contain the following elements:

colName。出力列の名前。colName Is the name for the output column.

OPENJSON は既定では、列の名前を使用して、JSON テキストのプロパティと一致します。By default, OPENJSON uses the name of the column to match a property in the JSON text. たとえば、nameの列をスキーマを指定する場合、OPENJSON は JSON テキストには、"name"プロパティを使用して、この列を作成しようとします。For example, if you specify the column name in the schema, OPENJSON tries to populate this column with the property "name" in the JSON text. 使用して、この既定のマッピングをオーバーライドすることができます、 column_path 引数。You can override this default mapping by using the column_path argument.

typetype
出力列のデータ型です。Is the data type for the output column.

注意

AS JSON オプションも使用する場合は、列の typeNVARCHAR(MAX) にする必要があります。If you also use the AS JSON option, the column type must be NVARCHAR(MAX).

column_pathcolumn_path
指定された列で返されるプロパティを指定する JSON のパスです。Is the JSON path that specifies the property to return in the specified column. 詳細については、の説明を参照してください、path このトピックの前のパラメーター。For more info, see the description of the path parameter previously in this topic.

出力列の名前がプロパティの名前の一致しない場合に既定のマッピング ルールをオーバーライドするには、column_path を使用します。Use column_path to override default mapping rules when the name of an output column doesn't match the name of the property.

JSON の式のプロパティを持つパスの手順を照合に使用する比較では、大文字と小文字および照合順序に関係なく (つまり、BIN2 の比較)。The comparison used to match path steps with the properties of the JSON expression is case-sensitive and collation-unaware (that is, a BIN2 comparison).

パスの詳細については、「JSON パス式 (SQL Server)」を参照してください。For more info about paths, see JSON Path Expressions (SQL Server).

AS JSONAS JSON
参照先のプロパティに内部 JSON オブジェクトまたは配列が含まれていることを指定するには、列定義の中で AS JSON を使用します。Use the AS JSON option in a column definition to specify that the referenced property contains an inner JSON object or array. AS JSON を指定する場合、列の型は NVARCHAR(MAX) にする必要があります。If you specify the AS JSON option, the type of the column must be NVARCHAR(MAX).

  • として AS JSON を指定しないと、列の場合、関数は、指定されたパスに指定された JSON プロパティからスカラー値 (たとえば、int、文字列、true、false) を返します。If you don't specify AS JSON for a column, the function returns a scalar value (for example, int, string, true, false) from the specified JSON property on the specified path. パスがオブジェクトまたは配列を表しているときに、指定されたパスにプロパティが見つからない場合、関数は、lax モードでは null を、strict モードではエラーを返します。If the path represents an object or an array, and the property can't be found at the specified path, the function returns null in lax mode or returns an error in strict mode. この動作は、JSON_VALUE 関数の動作に似ています。This behavior is similar to the behavior of the JSON_VALUE function.

  • AS JSON の列を指定する場合、関数は、指定したパス指定された JSON プロパティからは JSON フラグメントを返します。If you specify AS JSON for a column, the function returns a JSON fragment from the specified JSON property on the specified path. パスがスカラー値を表しているときに、指定されたパスにプロパティが見つからない場合、関数は、lax モードでは null を、strict モードではエラーを返します。If the path represents a scalar value, and the property can't be found at the specified path, the function returns null in lax mode or returns an error in strict mode. この動作は、JSON_QUERY 関数の動作に似ています。This behavior is similar to the behavior of the JSON_QUERY function.

注意

JSON プロパティから入れ子になった JSON フラグメントを返す場合は、AS JSON フラグを指定する必要があります。If you want to return a nested JSON fragment from a JSON property, you have to provide the AS JSON flag. このオプションの指定がないときに、プロパティが見つからない場合、OPENJSON は、参照先の JSON オブジェクトまたは配列の代わりに NULL 値を返します。Without this option, if the property can't be found, OPENJSON returns a NULL value instead of the referenced JSON object or array, or it returns a run-time error in strict mode.

たとえば、次のクエリを返し、配列の要素の書式を設定します。For example, the following query returns and formats the elements of an array:

DECLARE @json NVARCHAR(MAX) = 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',  
              [Order]  nvarchar(MAX)  AS JSON  
 )

結果Results

数値Number dateDate CustomerCustomer QuantityQuantity Order
SO43659SO43659 2011-05-により、2011-05-31T00:00:00 AW29825AW29825 11 {"Number":"SO43659","Date":"2011-05-31T00:00:00"}{"Number":"SO43659","Date":"2011-05-31T00:00:00"}
として SO43661SO43661 2011-06-01T00:00:002011-06-01T00:00:00 AW73565AW73565 33 {"Number":"SO43661","Date":"2011-06-01T00:00:00"}{"Number":"SO43661","Date":"2011-06-01T00:00:00"}

戻り値Return Value

OPENJSON 関数によって返される列は、WITH オプションによって異なります。The columns that the OPENJSON function returns depend on the WITH option.

  1. OPENJSON を既定のスキーマで呼び出した場合 (つまり、WITH 句に明示的にスキーマを指定しない場合)、関数は、次の列を持つテーブルを返します。When you call OPENJSON with the default schema - that is, when you don't specify an explicit schema in the WITH clause - the function returns a table with the following columns:

    1. [キー]Key. 指定した配列内の要素のインデックスまたは指定したプロパティの名前を含む、nvarchar (4000) 値です。An nvarchar(4000) value that contains the name of the specified property or the index of the element in the specified array. キーの列では、BIN2 照合順序を持っています。The key column has a BIN2 collation.
    2. Value. プロパティの値を含む、nvarchar (max) 値です。An nvarchar(max) value that contains the value of the property. [値] 列では、その照合順序を継承 jsonExpression から。*The value column inherits its collation from jsonExpression.
    3. Type. 値の型を含む整数値。An int value that contains the type of the value. の列には、既定のスキーマを OPENJSON を使用する場合にのみが返されます。The Type column is returned only when you use OPENJSON with the default schema. 型の列では、次の values.* * のことがあります。The type column has one of the following values:

      型の列の値Value of the Type column JSON データ型JSON data type
      00 nullnull
      11 stringstring
      22 ssNoversionint
      33 true/falsetrue/false
      44 arrayarray
      55 objectobject

      * * 最初のレベル プロパティのみが返されます。Only first-level properties are returned. JSON のテキストが正しくフォーマットされていない場合、ステートメントが失敗します。The statement fails if the JSON text is not properly formatted.

  2. -OPENJSON を呼び出すし、WITH 句で、明示的なスキーマを指定する、するときに、WITH 句で定義したスキーマを持つテーブルを返します。When you call OPENJSON and you specify an explicit schema in the WITH clause, the function returns a table with the schema that you defined in the WITH clause.

RemarksRemarks

OPENJSON の 2 番目の引数または with_clause で使用される json_path は、lax または strict キーワードで始めることができます。json_path used in the second argument of OPENJSON or in with_clause can start with the lax or strict keyword.

  • lax モードでは、指定のパスにオブジェクトまたは値が見つからない場合でも、OPENJSON はエラーを生成しません。In lax mode, OPENJSON doesn't raise an error if the object or value on the specified path can't be found. パスが見つからない場合、OPENJSON は、空の結果セットまたは NULL 値を返します。If the path can't be found, OPENJSON returns either an empty result set or a NULL value.
  • Strict モード では、パスが見つからない場合、OPENJSON はエラーを返します。In strict, mode OPENJSON returns an error if the path can't be found.

このページに示すいくつかの例では、パス モード (lax または strict) を明示的に指定しています。Some of the examples on this page explicitly specify the path mode, lax or strict. パス モードの指定は必須ではありません。The path mode is optional. パス モードを明示的に指定しない場合は、lax が既定のモードになります。If you don't explicitly specify a path mode, lax mode is the default. パス モードとパス式の詳細については、「JSON パス式 (SQL Server)」を参照してください。For more info about path mode and path expressions, see JSON Path Expressions (SQL Server).

with_clause の列名は、JSON テキスト内のキーと照合されます。Column names in with_clause are matched with keys in the JSON text. 列名 [Address.Country] を指定した場合は、Address.Country キーと照合されます。If you specify the column name [Address.Country], it's matched with the key Address.Country. オブジェクト Address 内の入れ子になったキー Country を参照する場合は、パス $.Address.Country を列のパスに指定する必要があります。If you want to reference a nested key Country within the object Address, you have to specify the path $.Address.Country in column path.

json_path には、英数字を使用したキーを含めることがあります。json_path may contain keys with alphanumeric characters. キーに特殊文字がある場合は、二重引用符を使用して、json_path 内でキー名をエスケープします。Escape the key name in json_path with double quotes if you have special characters in the keys. たとえば、$."my key $1".regularKey."key with . dot" は、次の JSON テキストの値 1 と一致します。For example, $."my key $1".regularKey."key with . dot" matches value 1 in the following JSON text:

{
  "my key $1": {
    "regularKey":{
      "key with . dot": 1
    }
  }
}

使用例Examples

例 2 - JSON 配列を一時テーブルに変換します。Example 1 - Convert a JSON array to a temporary table

次の例では、識別子の一覧を数値の JSON 配列として指定します。The following example provides a list of identifiers as a JSON array of numbers. このクエリは、JSON 配列を識別子のテーブルに変換し、すべての製品を指定のID でフィルター処理します。The query converts the JSON array to a table of identifiers and filters all products with the specified ids.

DECLARE @pSearchOptions NVARCHAR(4000) = N'[1,2,3,4]'

SELECT *
FROM products
INNER JOIN OPENJSON(@pSearchOptions) AS productTypes
 ON product.productTypeID = productTypes.value

このクエリは、次の例と同等です。This query is equivalent to the following example. ただし、次の例では、数値をパラメーターとして渡す代わりに、クエリに埋め込む必要があります。However, in the example below, you have to embed numbers in the query instead of passing them as parameters.

SELECT *
FROM products
WHERE product.productTypeID IN (1,2,3,4)

例 3 - 2 つの JSON オブジェクトからのマージ プロパティExample 2 - Merge properties from two JSON objects

次の例では、2 つの JSON オブジェクトのすべてのプロパティの和集合を選択します。The following example selects a union of all the properties of two JSON objects. 2 つのオブジェクトでは、重複するnameプロパティがあります。The two objects have a duplicate name property. 例では、キーの値を使用して、重複する行を結果から除外します。The example uses the key value to exclude the duplicate row from the results.

DECLARE @json1 NVARCHAR(MAX),@json2 NVARCHAR(MAX)

SET @json1=N'{"name": "John", "surname":"Doe"}'

SET @json2=N'{"name": "John", "age":45}'

SELECT *
FROM OPENJSON(@json1)
UNION ALL
SELECT *
FROM OPENJSON(@json2)
WHERE [key] NOT IN (SELECT [key] FROM OPENJSON(@json1))

例 3 - CROSS APPLY を使用してテーブルのセルに格納されている JSON データを行と結合するExample 3 - Join rows with JSON data stored in table cells using CROSS APPLY

次の例では、SalesOrderHeader テーブルには、SalesOrderReasons の配列を JSON 形式で含んでいるSalesReason テキスト列があります。In the following example, the SalesOrderHeader table has a SalesReason text column that contains an array of SalesOrderReasons in JSON format. SalesOrderReasons オブジェクトには、QualityManufacturer などのプロパティが含まれます。The SalesOrderReasons objects contain properties like Quality and Manufacturer. 例では、販売注文のすべての行を関連する販売理由に結合するレポートを作成します。The example creates a report that joins every sales order row to the related sales reasons. OPENJSON 演算子は、理由は、1 つの子テーブルに格納されていたかのように、販売理由の JSON 配列を展開します。The OPENJSON operator expands the JSON array of sales reasons as if the reasons were stored in a separate child table. そして、CROSS APPLY 演算子は、OPENJSON テーブル値関数によって返される行に各販売注文の行を結合します。Then the CROSS APPLY operator joins each sales order row to the rows returned by the OPENJSON table-valued function.

SELECT SalesOrderID,OrderDate,value AS Reason
FROM Sales.SalesOrderHeader
CROSS APPLY OPENJSON(SalesReasons)

ヒント

通常使用順に展開するときに、JSON 配列は、個々 のフィールドに格納されているし、その親の行との結合に、 Transact-SQLTransact-SQL CROSS APPLY 演算子。When you have to expand JSON arrays stored in individual fields and join them with their parent rows, you typically use the Transact-SQLTransact-SQL CROSS APPLY operator. 詳細については、CROSS APPLY、を参照してください。 FROM & #40 です。TRANSACT-SQL と #41;.For more info about CROSS APPLY, see FROM (Transact-SQL).

返される行の明示的に定義されたスキーマを指定した OPENJSON を使用することで、同じクエリを書き直すことができます。The same query can be rewritten by using OPENJSON with an explicitly defined schema of rows to return:

SELECT SalesOrderID, OrderDate, value AS Reason  
FROM Sales.SalesOrderHeader  
     CROSS APPLY OPENJSON (SalesReasons) WITH (value nvarchar(100) '$')

この例では、$ パスは、配列内の各要素を参照します。In this example, the $ path references each element in the array. 返される値を明示的にキャストする場合は、この種類のクエリを使用できます。If you want to explicitly cast the returned value, you can use this type of query.

例 4 - CROSS APPLY とリレーショナル行と JSON の要素を結合するExample 4 - Combine relational rows and JSON elements with CROSS APPLY

次のクエリは、次の表に示すように、リレーショナル行と JSON 要素を結果内で結合します。The following query combines relational rows and JSON elements into the results shown in the following table.

SELECT store.title, location.street, location.lat, location.long  
FROM store  
CROSS APPLY OPENJSON(store.jsonCol, 'lax $.location')   
     WITH (street varchar(500) ,  postcode  varchar(500) '$.postcode' ,  
     lon int '$.geo.longitude', lat int '$.geo.latitude')  
     AS location

結果Results

titletitle streetstreet 郵便番号postcode lonlon latlat
全体の食品市場Whole Food Markets 17991 Redmond の方法17991 Redmond Way WA 98052WA 98052 47.66612447.666124 -122.10155-122.10155
SearsSears 148th Ave NE148th Ave NE WA 98052WA 98052 47.6302447.63024 -122.141246,17-122.141246,17

例 5 - インポートには、SQL Server の JSON データExample 5 - Import JSON data into SQL Server

次の例では、全体の JSON オブジェクトを SQL ServerSQL Server テーブルです。The following example loads an entire JSON object into a SQL ServerSQL Server table.

DECLARE @json NVARCHAR(max)  = N'{  
  "id" : 2,  
  "firstName": "John",  
  "lastName": "Smith",  
  "isAlive": true,  
  "age": 25,  
  "dateOfBirth": "2015-03-25T12:00:00",  
  "spouse": null  
  }';  

  INSERT INTO Person  
  SELECT *   
  FROM OPENJSON(@json)  
  WITH (id int,  
        firstName nvarchar(50), lastName nvarchar(50),   
        isAlive bit, age int,  
        dateOfBirth datetime2, spouse nvarchar(50))

参照See Also

JSON パス式 (SQL Server) JSON Path Expressions (SQL Server)
OPENJSON を使用して JSON データを行と列に変換する (SQL Server) Convert JSON Data to Rows and Columns with OPENJSON (SQL Server)
既定のスキーマを使用する OPENJSON の使用(SQL Server) Use OPENJSON with the Default Schema (SQL Server)
明示的なスキーマで OPENJSON を使用する(SQL Server)Use OPENJSON with an Explicit Schema (SQL Server)