OPENJSON (Transact-SQL)

Applies to: SQL Server 2016 (13.x) and later versions Azure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsSQL analytics endpoint in Microsoft FabricWarehouse in Microsoft FabricSQL database in Microsoft Fabric

OPENJSONテーブル値関数は、JSON テキストを解析し、JSON 入力から行と列としてオブジェクトとプロパティを返します。 つまり、 OPENJSON は JSON ドキュメントに対する行セット ビューを提供します。 行セット内の列と、それらの列に入力するために使用する JSON プロパティのパスを明示的に指定できます。 OPENJSONは一連の行を返すので、他のテーブル、ビュー、またはテーブル値関数を使用できるのと同様に、Transact-SQL ステートメントのFROM句でOPENJSONを使用できます。

OPENJSONを使用して、JSON データを SQL Server にインポートするか、JSON データを JSON を直接使用できないアプリまたはサービスのリレーショナル形式に変換します。

Transact-SQL 構文表記規則

Syntax

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

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

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 can contain nested sub-objects. If you want to parse a sub-object from within jsonExpression, you can specify a path parameter for the JSON sub-object.

openjson

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. As an alternative, you can explicitly specify the schema of the result set that OPENJSON returns by providing with_clause.

with_clause

The with_clause contains a list of columns with their types for OPENJSON to return. 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). If a column name does not match a key name, you can provide an optional column_path, which is a JSON Path Expressions that references a key within the jsonExpression.

Arguments

jsonExpression

JSON テキストを含む Unicode 文字式です。

OPENJSON では、配列の要素または JSON 式内のオブジェクトのプロパティを反復処理し、各要素またはプロパティの 1 つの行を返します。 The following example returns each property of the object provided as jsonExpression:

DECLARE @json NVARCHAR(2048) = N'{
   "String_value": "John",
   "DoublePrecisionFloatingPoint_value": 45,
   "DoublePrecisionFloatingPoint_value": 2.3456,
   "BooleanTrue_value": true,
   "BooleanFalse_value": false,
   "Null_value": null,
   "Array_value": ["a","r","r","a","y"],
   "Object_value": {"obj":"ect"}
}';

SELECT * FROM OpenJson(@json);

Results:

• DoublePrecisionFloatingPoint_value は、IEEE 754 に準拠します。

path

Is an optional JSON path expression that references an object or an array within jsonExpression. OPENJSON は、指定された位置にある JSON テキストをシークし、参照されているフラグメントのみを解析します。 詳細については、「 JSON パス式」を参照してください。

You can provide a variable as the value of path. (これは、SQL Server 2016 (13.x) 以前のバージョンではサポートされていません)。

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

OPENJSONが JSON 配列を解析すると、JSON テキスト内の要素のインデックスがキーとして返されます。

JSON の式のプロパティを持つパスの手順を照合に使用する比較では、大文字と小文字および照合順序に関係なく (つまり、BIN2 の比較)。

配列要素の ID

Azure Synapse Analytics のサーバーレス SQL プールの OPENJSON 関数を使用すると、結果として返される各行の ID を自動的に生成できます。 この ID 列は、列定義の後の JSON パスの式 $.sql:identity() を使用して指定されます。 JSON パス式のこの値を持つ列は、関数が解析する JSON 配列内の各要素に対して一意の 0 から始まる数値を生成します。 ID 値は、配列要素の位置またはインデックスを表します。

DECLARE @array VARCHAR(MAX);
SET @array = '[{"month":"Jan", "temp":10},{"month":"Feb", "temp":12},{"month":"Mar", "temp":15},
               {"month":"Apr", "temp":17},{"month":"May", "temp":23},{"month":"Jun", "temp":27}
              ]';

SELECT * FROM OPENJSON(@array)
        WITH (  month VARCHAR(3),
                temp int,
                month_id tinyint '$.sql:identity()') as months

Results

ID は、Synapse Analytics のサーバーレス SQL プールでのみ使用できます。

with_clause

OPENJSON関数が返す出力スキーマを明示的に定義します。 The optional with_clause can contain the following elements:

colName

出力列の名前。

既定では、 OPENJSON は列の名前を使用して JSON テキスト内のプロパティと一致します。 たとえば、スキーマで列 name を指定した場合、 OPENJSON は JSON テキストのプロパティ "name" をこの列に設定しようとします。 You can override this default mapping by using the column_path argument.

type

出力列のデータ型。

column_path

指定した列で返されるプロパティを指定する JSON のパス。 For more info, see the description of the path parameter previously in this topic.

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 の比較)。

パスの詳細については、「 JSON パス式」を参照してください。

AS JSON

列定義の AS JSON オプションを使用して、参照先のプロパティに内部 JSON オブジェクトまたは配列が含まれていることを指定します。 If you specify the AS JSON option, the type of the column must be nvarchar(MAX).

• 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 モードでエラーを返します。 この動作は、 JSON_VALUE 関数の動作に似ています。

• 列に AS JSON を指定すると、指定したパスの指定した JSON プロパティから JSON フラグメントが返されます。 パスがスカラー値を表し、指定したパスでプロパティが見つからない場合、関数はlax モードでNULLを返すか、strict モードでエラーを返します。 この動作は、 JSON_QUERY 関数の動作に似ています。

たとえば、次のクエリを返し、配列の要素の書式を設定します。

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

Return value

OPENJSON関数が返す列は、WITH オプションによって異なります。

• 既定のスキーマで OPENJSON を呼び出すと(つまり、 WITH 句で明示的なスキーマを指定しない場合)、関数は次の列を持つテーブルを返します。

  • Key. An nvarchar(4000) value that contains the name of the specified property or the index of the element in the specified array. key列には BIN2 照合順序があります。

  • Value. An nvarchar(MAX) value that contains the value of the property. The value column inherits its collation from jsonExpression.

  • Type. An int value that contains the type of the value. Type列は、既定のスキーマでOPENJSONを使用する場合にのみ返されます。 type列には、次のいずれかの値があります。

    型の列の値	JSON データ型
    0	null 値
    1	文字列
    2	number
    3	true/false
    4	アレイ
    5	オブジェクト

  最初のレベル プロパティのみが返されます。 JSON のテキストが正しくフォーマットされていない場合、ステートメントが失敗します。

• OPENJSONを呼び出し、WITH句で明示的なスキーマを指定すると、WITH句で定義したスキーマを持つテーブルが返されます。

Remarks

json_path used in the second argument of OPENJSON or in with_clause can start with the lax or strict keyword.

• lax モードでは、指定したパスのオブジェクトまたは値が見つからない場合、OPENJSONはエラーを発生しません。 パスが見つからない場合、 OPENJSON は空の結果セットまたは NULL 値を返します。
• strictでは、モード OPENJSONはパスが見つからない場合にエラーを返します。

このページの一部の例では、パス モード、 lax 、または strictを明示的に指定します。 パス モードの指定は必須ではありません。 パス モードを明示的に指定しない場合は、 lax モードが既定です。 パス モードとパス式の詳細については、「 JSON パス式」を参照してください。

Column names in with_clause are matched with keys in the JSON text. 列名 [Address.Country] を指定した場合は、Address.Country キーと照合されます。 オブジェクト Country 内の入れ子になったキー Address を参照する場合は、パス $.Address.Country を列のパスに指定する必要があります。

json_path can contain keys with alphanumeric characters. 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 と一致します。

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

Examples

例 2 - JSON 配列を一時テーブルに変換します。

次の例では、識別子の一覧を数値の JSON 配列として指定します。 このクエリは、JSON 配列を識別子のテーブルに変換し、すべての製品を指定の ID でフィルター処理します。

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

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

このクエリは、次の例と同等です。 ただし、次の例では、数値をパラメーターとして渡す代わりに、クエリに埋め込む必要があります。

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

例 3 - 2 つの JSON オブジェクトからのマージ プロパティ

次の例では、2 つの JSON オブジェクトのすべてのプロパティの和集合を選択します。 The two objects have a duplicate name property. 例では、キーの値を使って、結果から重複する行を除外しています。

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 データを行と結合する

次の例では、SalesOrderHeader テーブルには、SalesReason の配列を JSON 形式で含んでいるSalesOrderReasons テキスト列があります。 The SalesOrderReasons objects contain properties like Quality and Manufacturer. 例では、すべての販売注文の行を、関連する販売理由と結合するレポートを作成しています。 OPENJSON演算子は、理由が別の子テーブルに格納されているかのように、販売理由の JSON 配列を拡張します。 次に、 CROSS APPLY 演算子は、各販売注文行を、 OPENJSON テーブル値関数によって返される行に結合します。

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

返される行の明示的に定義されたスキーマを指定した OPENJSON を使用することで、同じクエリを書き直すことができます。

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

この例では、$ パスは、配列内の各要素を参照します。 返される値を明示的にキャストする場合は、この種類のクエリを使用できます。

例 4 - CROSS APPLY とリレーショナル行と JSON の要素を結合する

次のクエリは、次の表に示すように、リレーショナル行と JSON 要素を結果内で結合します。

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

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

次の例では、全体の JSON オブジェクトを SQL Server テーブルです。

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 DATETIME, spouse NVARCHAR(50))

例 6 - JSON コンテンツを使用したシンプルな例

--simple cross apply example
DECLARE @JSON NVARCHAR(MAX) = N'[
{
"OrderNumber":"SO43659",
"OrderDate":"2011-05-31T00:00:00",
"AccountNumber":"AW29825",
"ItemPrice":2024.9940,
"ItemQuantity":1
},
{
"OrderNumber":"SO43661",
"OrderDate":"2011-06-01T00:00:00",
"AccountNumber":"AW73565",
"ItemPrice":2024.9940,
"ItemQuantity":3
}
]'

SELECT root.[key] AS [Order],TheValues.[key], TheValues.[value]
FROM OPENJSON ( @JSON ) AS root
CROSS APPLY OPENJSON ( root.value) AS TheValues

Related content

• JSON パス式
• OPENJSON を使用して JSON データを解析および変換する
• 既定のスキーマで OPENJSON を使用する
• 明示的なスキーマで OPENJSON を使用する