OPENJSON (Transact-SQL)

適用対象:Sql Server 2016 (13.x) 以降のバージョンAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsMicrosoft Fabric の AzureSQL 分析エンドポイントMicrosoft Fabric のウェアハウスMicrosoft Fabric のSQL データベース

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

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

Transact-SQL 構文表記規則

Syntax

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

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

OPENJSONテーブル値関数は、最初の引数として指定された jsonExpression を解析し、式内の JSON オブジェクトのデータを含む 1 つ以上の行を返します。 jsonExpression には、入れ子になったサブオブジェクトを含めることができます。 jsonExpression 内からサブオブジェクトを解析する場合は、JSON サブオブジェクトのパス パラメーターを指定できます。

openjson

既定では、OPENJSONテーブル値関数は、key:value で見つかった各ペアのキー名、値、および型を含む 3 つの列を返します。 代わりに、with_clauseを指定することで、 OPENJSON 返される結果セットのスキーマを明示的 に指定できます。

with_clause

with_clauseには、OPENJSONが返す型を含む列の一覧が含まれています。 既定では、OPENJSONは jsonExpression のキーとwith_clause内の列名を照合します (この場合、キーと一致することは、大文字と小文字が区別されることを意味します)。 列名がキー名と一致しない場合は、省略可能なcolumn_pathを指定できます。これは、jsonExpression 内のキーを参照する JSON パス式です。

Arguments

jsonExpression

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

OPENJSON では、配列の要素または JSON 式内のオブジェクトのプロパティを反復処理し、各要素またはプロパティの 1 つの行を返します。 次の例では、 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

jsonExpression 内のオブジェクトまたは配列を参照する省略可能な JSON パス式です。 OPENJSON は、指定された位置にある JSON テキストをシークし、参照されているフラグメントのみを解析します。 詳細については、「 JSON パス式」を参照してください。

パスの値として変数を指定できます。 (これは、SQL Server 2016 (13.x) 以前のバージョンではサポートされていません)。

次の例では、 パスを指定して入れ子になったオブジェクトを返します。

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関数が返す出力スキーマを明示的に定義します。 省略可能な with_clause には、次の要素を含めることができます。

colName(共同名)

出力列の名前。

既定では、 OPENJSON は列の名前を使用して JSON テキスト内のプロパティと一致します。 たとえば、スキーマで列 name を指定した場合、 OPENJSON は JSON テキストのプロパティ "name" をこの列に設定しようとします。 この既定のマッピングは 、column_path 引数を使用してオーバーライドできます。

type

出力列のデータ型。

column_path

指定した列で返されるプロパティを指定する JSON のパス。 詳細については、このトピックの パス パラメーターの説明を参照してください。

出力列の名前がプロパティの名前と一致しない場合は、 column_path を使用して既定のマッピング 規則をオーバーライドします。

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

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

AS JSON

列定義の AS JSON オプションを使用して、参照先のプロパティに内部 JSON オブジェクトまたは配列が含まれていることを指定します。 AS JSON オプションを指定する場合、列の型は nvarchar(MAX) である必要があります。

• 列に AS JSON を指定しない場合、関数は指定されたパスの指定された JSON プロパティからスカラー値 ( int、 string、 true、 false など) を返します。 パスがオブジェクトまたは配列を表し、指定したパスでプロパティが見つからない場合、関数はNULL モードでlaxを返すか、strict モードでエラーを返します。 この動作は、 JSON_VALUE 関数の動作に似ています。

• 列に AS JSON を指定すると、指定したパスの指定した JSON プロパティから JSON フラグメントが返されます。 パスがスカラー値を表し、指定したパスでプロパティが見つからない場合、関数はNULL モードでlaxを返すか、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

戻り値

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

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

  • Key。 指定したプロパティの名前または指定した配列内の要素のインデックスを含む nvarchar(4000) 値。 key列には BIN2 照合順序があります。

  • Value。 プロパティの値を含む nvarchar(MAX) 値。 value列は、jsonExpression から照合順序を継承します。

  • Type。 値の型を含む int 値。 Type列は、既定のスキーマでOPENJSONを使用する場合にのみ返されます。 type列には、次のいずれかの値があります。

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

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

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

Remarks

の 2 番目の引数または OPENJSON で使用キーワードまたは lax キーワードで始めることができます。

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

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

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

json_path には、英数字を含むキーを含めることができます。 キーに特殊文字がある場合は、 json_path のキー名を二重引用符でエスケープします。 たとえば、 $."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 オブジェクトのすべてのプロパティの和集合を選択します。 2 つのオブジェクトの 名前 プロパティが重複しています。 例では、キーの値を使って、結果から重複する行を除外しています。

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 テキスト列があります。 SalesOrderReasons オブジェクトには、Quality や 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

関連コンテンツ

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