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使用できる行のセットを返します、 OPENJSONで、FROMの句、 Transact-SQLTransact-SQLステートメントと同じように、その他のテーブル、ビュー、またはテーブル値関数を使用することができます。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

互換性レベル 120 には、新しい Azure SQL データベースであっても既定可能性があります。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 サブオブジェクトのパラメーターです。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テーブル値関数は、キー名、値が含まれている 3 つの列を返し、{キー: 値} の各ペアの型がで見つかったjsonExpressionです。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. 代わりに、明示的に指定できます、結果のスキーマを設定するOPENJSONにより返しますwith_clauseです。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 の句での構文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内のキーと一致するjsonExpressionで列名を持つwith_clause (この場合、一致するキーはことを意味、大文字小文字を区別)。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、これは、 JSON パス式内のキーを参照する、 jsonExpressionです。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
文字列値StringValue JohnJohn 11
IntValueIntValue DoeDoe 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

パスpath

オブジェクトまたは配列内で参照される省略可能な JSON パス式は、 jsonExpressionです。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 パス式 & #40 です。SQL Server ).For more info, see JSON Path Expressions (SQL Server).

SQL Server vNextSQL Server vNextし、 Azure SQL データベースAzure SQL Database、変数の値として使用できるパスです。In SQL Server vNextSQL Server vNext and in Azure SQL データベースAzure SQL Database, you can provide a variable as the value of 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

[キー]Key Value
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. たとえば、列を指定する名前スキーマでは、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.

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

注意

使用する場合は、 AS JSONオプション、列する必要がありますNVARCHAR(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. 詳細については、の説明を参照して、パスこのトピックの前のパラメーターです。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 パス式 & #40 です。SQL Server ).For more info about paths, see JSON Path Expressions (SQL Server).

JSON としてAS JSON
使用して、 AS JSON列定義のオプションは、参照されたプロパティには、内部の 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列、関数は、スカラー値を返す (たとえば、int、文字列、true、false)、指定された JSON プロパティから指定したパスにします。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 を返します。 または、厳格モードではエラーを返します。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 を返します。 または、厳格モードではエラーを返します。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 値が返されます。 プロパティが見つからない場合または strict モードで実行時エラーが返されます。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 日付Date CustomerCustomer QuantityQuantity Order
SO43659SO43659 2011-05-により、2011-05-31T00:00:00 AW29825AW29825 11 {"Number":"SO43659"、「日付」:"2011-05-により、"}{"Number":"SO43659","Date":"2011-05-31T00:00:00"}
として SO43661SO43661 2011-06-01T00:00:002011-06-01T00:00:00 AW73565AW73565 33 {"Number":"として SO43661"、「日付」:"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. 型の列は、次の値のいずれか。The type column has one of the following values:

      型の列の値Value of the Type column JSON データ型JSON data type
      00 nullnull
      11 stringstring
      22 intint
      33 true または falsetrue/false
      44 arrayarray
      55 オブジェクト (object)object

      最初のレベル プロパティのみが返されます。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.

解説Remarks

json_pathの 2 番目の引数で使用されるOPENJSONまたはwith_clauseからでも開始できます、 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.

このページの例のいくつかは、path モード、lax または strict を明示的に指定します。Some of the examples on this page explicitly specify the path mode, lax or strict. Path モードではオプションです。The path mode is optional. 厳密でないモードは、明示的に path モードを指定しない場合は、既定になります。If you don't explicitly specify a path mode, lax mode is the default. Path モードとパス式についての詳細については、次を参照してください。 JSON パス式 & #40 です。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. 入れ子になったキーを参照する場合は、Countryオブジェクト内でAddress、パスを指定する必要が$.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"と一致する値の 1 次の JSON テキストに。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

例 1: 一時テーブルを 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)

例 2: 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 つのオブジェクトがある重複名前プロパティです。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テーブルには、SalesReasonの配列を含むテキスト列SalesOrderReasonsJSON 形式でします。In the following example, the SalesOrderHeader table has a SalesReason text column that contains an array of SalesOrderReasons in JSON format. SalesOrderReasonsオブジェクトと同様にプロパティが含ま品質製造元です。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 パス式 & #40 です。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)